Temporary tables are tables which are created in the tempDB and are automatically deleted when they are not used. They act like an regular table and can be queried records and modify their value via update, delete, insert. If we are using temp table inside Sp they are automatically destroyed once the connection is closed.

The scope of the temp table is in the session in which it’s created.

Different type of Temp Table

• Local Temporary tables
• Global Temporary tables

Syntax

CREATE TABLE #MstCustomerUpload(ID int, NAME NVARCHAR(100),MOBILENO VARCHAR(10),PANNO NUMERIC(10,0))

When we create a temporary table we gave the name of the table with # which denotes that the table is local temporary table. All the temp table are stored in tempdb as shown below.

https://giphy.com/embed/3o7bugWhds34OM5E9q

Explicitly dropping the Temp table

drop table #MstCustomerUpload

Usually we use temp table in stored procedure where we want to stored the records in a table and query, update, insert, delete the records from the table. If we are creating the temp table inside the stored procedure, once the stored procedure get executed the temp table is automatically destroyed.

Simple stored procedure with Temp table

There are much better scenarios where we will leveraging usage of Temp table. For now let just focus on this small sceario where i am retrieving records from two table customer and cust and performing union operation and then insert the records to the temp table and retrieving records from the temp table to the user.

CREATE PROCEDURE spGetCustomerDetails
as
begin

create table #tmpCustomer(Id int identity(1,1) ,Name nvarchar(50),Email nvarchar(50))

insert into #tmpCustomer  
select top(2) Name,Email from [dbo].[Customer] 
union all
select top(2) Name,'' as Email from [dbo].[Cust] 

select * from #tmpCustomer;

end
go

Running the stored procedure

Global temporary table

In order create a global temporary table we use double ## symbols. The difference between local temp tables and global temo tables is global temp tables are available to all SQL connections and is destoyed when last connection is closed.

create table ##tmpCustomerDetails (Id int, Name nvarchar(50), Email nvarchar(50))

The table created for Global temporary table is same as the name defined by the user as compared to local temp table where SQL server add random values at the end.

Happy Learning