Temporary table / temp table in SQL Server

Temporary or Temp tables are tables that exist temporarily on the SQL Server.

It act like regular tables, so you can perform any query Insert , update and delete on it.

Temporary table name started with a “#” sign. This is the identifier for SQL Server that it is dealing with a temporary table.

Temporary tables are created in tempdb. The scope of Temporary table is session specific therefore it remains visible to the current user unless the current session ends or it is dropped explicitly.

Lets look at an example of SQL Server temporary table.

Following SQL Statement creates a temporary table in tempdb database and can be used only in current session that is used to create a temporary table.

create table #Emp (
EmpId int not null ,
EmpName varchar(100),
EmpCode varchar(10)
)

We can check whether a temp table is created or not by following below steps > Click on databases >tempdb > temporary tables or we can also check it by using T-SQL Query 

As we can see temporary table is create in tempdb database but it will be accessible only to current session on which it is created .

We can get current session Id by following query on our query editor window

Select @@SPID as SessionId

Or we can also see the session Id at on the right bottom of current query editor window on which we created a temporary table.

So it means user can access this table only for session Id =52 , it will not be accessible to any other session Id.

Temporary temp and its scope

Temporary table is session specific table , so it will be accessible to current session only ,

As we have checked that temporary table is created on session Id = 52 , Lets select a table in session Id =52 , means just select temporary table in current open editor window .

It returns session Id =52  and blank table records ,which means we are able to access temporary table in current session.

Select @@SPID as CurrentSessionId

Select * from #Emp

Now Lets check it for another session , Just open another query editor window then try to access temporary table.

As we can see , we are now at another query editor window and this time our session Id is = 53.

Select @@SPID as CurrentSessionId

now we try to access temporary table in this session.

As we can see , it is not accessible for another session , and gives an error.

Create temporary table using select into statement

We can also create temporary table by using select into command , it just create a temporary table and copy the data of another table into temporary table.

 Select * into #empdata from DimEmployee

 

Insert , Update and Delete with Temporary Table

We can also perform DML operation with temp table similar like as we do with a table in SQL Server.

INSERT INTO #EmpData (FirstName ,LastName,MiddleName ,HireDate)
VALUES('Jhone' ,'Anthony','Mark' ,'2018-12-05') ;

UPDATE #EmpData SET FirstName ='Jack Rozer' WHERE Employeekey =5010 ;

DELETE FROM #EmpData WHERE Employeekey =5010;