Global Temp Table
The Global Temp Table is created using double ‘##‘ as prefix to the temporary table name. The global temp table is visible to all sessions. The global temp tables, once created will exist until the SQL Server is re-started or deleted by user. The global Temp Table can be useful in situations like, when we need to share temporary data across multiple sessions. Another important advantage of global Temp Table is that it can be created using dynamic SQL and used from outside of the dynamic SQL, where as Local Temp Tables created from inside a dynamic SQL is not visible outside of the dynamic SQL which created the temp table as its visibility is limited to the session of local SQL created it.
Example of Global Temp Table
We’re going to look at some examples of Global Temp Table to help you understand the topics better.
Exmaple 1:
DECLARE @SQLStatement NVARCHAR (1000);
SET @SQLStatement = 'CREATE TABLE ##MyTempTable (SNo INT,StudName varchar(100));';
EXEC sp_executesql @SQLStatement;
Insert into ##MyTempTable values (101,'Rajesh')
SELECT * FROM ##MyTempTable;
Drop Table ##MyTempTable
Explanation:
This SQL code dynamically creates a global temporary table, inserts a row into it, selects all rows from it, and then drops the table. The use of global temporary tables allows for temporary storage of data that is visible across different sessions, and the table is automatically dropped when the session that created it ends.
Output:
Example 2:
Create Global Temp Table using a stored procedure:
Create Procedure TempTabGlobalDemo
(
@TTabName varchar(50),
@CodeNo int
)
As
Begin
EXEC (
'create table ##' + @TTabName + ' (IdNo int)
insert ##' + @TTabName + ' values(' + @CodeNo + ')
insert ##' + @TTabName + ' values(' + @CodeNo + '+ 1 )
insert ##' + @TTabName + ' values(' + @CodeNo + '+ 2 )
Select * from ##' + @TTabName
)
EXEC('Drop Table ##' + @TTabName )
End
Execute the stored procedure ‘TempTabGlobalDemo’ created above with sample data, as below:
EXEC TempTabGlobalDemo 'GTempTableDem',101
Expalantion:
This stored procedure dynamically creates a global temporary table, inserts rows into it based on the input parameter @CodeNo
, selects data from the table, and finally drops the table. The dynamic SQL allows for flexibility in generating table names and executing the necessary SQL statements.
Output:
Dynamic SQL and Temporary Tables in SQL Server
In SQL Server, creating and using Temp Tables using dynamic SQL is a good feature when we need to temporarily create tables at run time and delete automatically all within a session. They can be very useful when we need to store temporary data in a structured format and do data manipulation using Data Manipulation Language in SQL. In this article let us discuss Temp Tables or Temporary Tables and their uses with examples.