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
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.
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.