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.
Execute the statement given below.
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.
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.
Summary
- What are Temporary Tables?
- Types of Temporary Tables.
- How to implement, using stored procedure in SSQL Server.
- Difference between local temp table and global temp table.