Types of Temp Tables
When creating Temp Tables, the scope and lifetime of the temporary tables should be taken into consideration. So based on the scope and lifetime Temp Tables can be of two types, namely Local Temp Table and Global Temp Table.
Local Temp Table
The local Temp Table is created with single ‘#‘ sign as prefix to the table name.
They are visible only to the session that creates the Temp Table and it is automatically dropped when the session ends.
Example 1:
CREATE TABLE #tmpStudDemo (id INT,StudName varchar(100))
EXEC ('insert #tmpStudDemo values(101,''Bharath'')')
SELECT * FROM #tmpStudDemo
Explanation:
T-SQL code creates a local temporary table #tmpStudDemo, inserts a single row with values (101, 'Bharath') into the table using dynamic SQL, and then selects all rows from the table. Local temporary tables are session-specific, and they are automatically dropped when the session that created them ends.
Output:
Local Temp Table
Example 2:
Create Local Temp Table using a stored procedure:
Create Procedure TempTabDemo
(
@TTabName varchar(50),
@CodeNo int
)
As
Begin
EXEC (
'create table #' + @TTabName + ' (IdNo int)
insert #' + @TTabName + ' values(' + @CodeNo + ')
insert #' + @TTabName + ' values(' + @CodeNo + '+ 1 )
Select * from #' + @TTabName
)
End
Execute the stored procedure ‘TempTabDemo’ created above with sample data, as below:
EXEC TempTabDemo 'TempTableDem',101
Explanation:
This stored procedure dynamically creates a local temporary table, inserts rows into it based on the input parameter @CodeNo, selects data from the table, and performs these actions within a single session. The dynamic SQL allows for flexibility in generating table names and executing the necessary SQL statements.
Output:
Local Temp Table from Stored Procedure
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.