Local And Global Temporary Tables Using Stored Procedure In SQL Server

Blog

Introduction

Temporary tables are created in TempDB database and are automatically deleted, when they are no longer used.

Description

In SQL Server, there are 2 types of temporary tables - Local Temporary tables and Global Temporary tables.

Local Temp tables are prefixed with single pound (#) symbol. Global temp tables are prefixed with 2 pound (##) symbols.

Local temporary tables are only visible to that session of SQL Server, which has created it whereas Global temporary tables are visible to all SQL Server sessions.

Steps to follow

Script to create Local Temporary table, using stored procedure is given below.

Create Procedure Sp_localTempTable
as
Begin
Create Table #MyDetails(Id int, Name nvarchar(20))
Insert into #MyDetails Values(1, 'SATYA1')
Insert into #MyDetails Values(2, 'SATYA2')
Insert into #MyDetails Values(3, 'SATYA3')
Select * from #MyDetails
End

Now, execute the script given below.

exec Sp_localTempTable

Execute the statement given below.

Select * from #MyDetails

If no local temporary table is found, then check the script given below.

select name from tempdb..sysobjects
where name like '#MyDetails%'

A local temporary table is available only for the connection, which has created the table. If you open another query Window and execute the query given below you get an error stating 'Invalid object name #MyDetails'.

Script to create Global Temporary table, using stored procedure is given below.

Create Procedure Sp_GlobalTempTable
as
Begin
Create Table ##MyDetails(Id int, Name nvarchar(20))
Insert into ##MyDetails Values(1, 'SATYA1')
Insert into ##MyDetails Values(2, 'SATYA2')
Insert into ##MyDetails Values(3, 'SATYA3')
Select * from ##MyDetails
End

Now, execute the scriptgiven below.

exec Sp_GlobalTempTable

Execute the statement given below.

Select * from ##MyDetails

Global temporary table is found to check the script given below.

select name from tempdb..sysobjects
where name like '##MyDetails%'

Notes

If the local temporary table is created inside the stored procedure, it gets dropped automatically upon the completion of the stored procedure execution but it can't happen for global temporary table.

The stored procedure creates #MyDetails local temporary table, populates it and then finally returns the data and destroys the local temporary table immediately after the completion of the stored procedure execution.

The stored procedure creates ##MyDetails global temporary table, populates it and then finally returns the data and destroys the global temporary table immediately after the completion of the stored procedure execution.

SQL Server appends the random number at the end of the local temp table name. There will be no random numbers suffixed at the end of the global temp table name.