Using temp table in Stored Procedure of SQL Server with practical examples.

What is temp table in SQL Server?

Temp tables are mostly used inside stored procedure of SQL Server.This kind of situation araises when developer needs to insert records from two or more tables and wants to return the common table.

Temporary tables are created inside TempDB database and are deleted automatically after their usage gets over.
There are 2 types of temporary tables
a) Local Temporary table
b) Global Temporary table

Local Temporary table:

Local temporary tables are prefixed with one pound(#) symbol.Local temporary tables are visible only for that session in SQL Server.

How to create Local temporary table in Stored Procedure of SQL Server?

                    Create Procedure Sp_localTempTableExample  
                    as  
                    Begin  
                    Create Table #UserDetails(UserId int, UserName nvarchar(20))  
  
                    Insert into #UserDetails Values(1, 'SHIVA')  
                    Insert into #UserDetails Values(2, 'MURUGA')  
                    Insert into #UserDetails Values(3, 'VINAYAGAR')  
  
                    Select * from #UserDetails  
                    End 

Execute local temporary table stored procedure:

exec Sp_localTempTableExample

How to view local temporary table details?

                Select * from #UserDetails

Note: A local temporary table will be available only for that session.If another session is opened and queried the temporary table then the local temporary table will not be visible.

Global Temporary table:

Global temporary tables are prefixed with two pound(##) symbol.Global temporary tables are visible to all sessions of SQL Server.

How to create Global temporary table in Stored Procedure of SQL Server?

                    Create Procedure Sp_GlobalTempTableExample  
                    as  
                    Begin  
                    Create Table ##UserDetails(UserId int, UserName nvarchar(20))  
  
                    Insert into ##UserDetails Values(1, 'SHIVA')  
                    Insert into ##UserDetails Values(2, 'MURUGA')  
                    Insert into ##UserDetails Values(3, 'VINAYAGAR')  
  
                    Select * from ##UserDetails  
                    End 

Execute Global temporary table stored procedure:

exec Sp_GlobalTempTableExample

How to view Global temporary table details?

                Select * from ##UserDetails

Note: The local temporary table which is used inside the stored procedure will be dropped automatically after the completion of stored procedure execution.