TEMPORARY TABLES:

Temporary tables are tables available only to the session that created them. 

These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table that helps the developer in a great way. These tables can be created at runtime and can do many kinds of operations that a normal table can do. In SQL Server all temporary tables are present in the tempdb database.

Types of Temporary Tables

SQL Server contains the following two types of Temporary tables.

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables:
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables starts with (“#”) hash sign.



Example

1. CREATE TABLE #TEMP_SAVE  
2. (  
3.    Id INT,  
4.    Name VARCHAR(30),  
5.    Date DATETIME DEFAULT GETDATE()  
6. )  

Global Temporary Tables
Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of global temp tables start with (“##”) double hash sign.

Example

1. CREATE TABLE ##TEMP_SAVE  
2. (  
3.    Id INT,  
4.    Name VARCHAR(30),  
5.    Date DATETIME DEFAULT GETDATE()  
6. )  

Now we will examine some examples of temp tables.

Example 1
Create a local temp table.

1. CREATE TABLE #TEMP_SAVE  
2. (  
3.    Id INT,  
4.    Name VARCHAR(30),  
5.    Age int   
6. )  

Now insert data into the local table.

1. Insert into #TEMP_SAVE  
2. Select 1,'pankaj',20 Union All  
3. Select 2,'Rahul',21 Union All  
4. Select 3,'Sandeep',22 Union All  
5. Select 4,'Sanjeev',23 Union All  
6. Select 5,'Neeraj',24   

Fetch data from the local temp table.

1. Select * From #TEMP_SAVE  

Drop the local temp table.

1. Drop Table #TEMP_SAVE  

Example 2
Create a global temp table.

1. Create Table ##TEmp  
2. (  
3.    IID int identity(1,1),  
4.    Name nvarchar(50),  
5.    Salary int  
6. )  

Insert data into the global temp table.

1. Insert Into ##TEmp  
2. Select 'Pankaj', 25000 Union All  
3. Select 'Rahul', 24000 Union All  
4. Select 'Sanjeev', 23000 Union All  
5. Select 'Sandeep', 22000 Union All  
6. Select 'Naru', 27000   

Fetch data the from global temp table.

1. Select * From ##Temp  

Drop the temp table.

1. Drop Table ##TEmp  

Example 3
We have an Employee table that look like the following:



Now we will create a local Temp using the “Select Into” command.

1. Select * Into #Temp_Loc  
2. From Employee  
3. Select * From #Temp_Loc  

Output



Example 4
Now we will try to create a view for the temp table.

1. Create View My_View   
2. as  
3. Select * from #Temp_Loc  
4. Where Emp_Id>3  

Output
Msg 4508, Level 16, State 1, Procedure My_View, Line 4 Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.

So this example illustrates that we cannot create a view for a temp table.

Example 5

1. Select * From ##TEmp  
2. Union All  
3. Select * From #TEMP_SAVE  

Output



This example illustrates that we can use Union and Union All for temp tables.

Example 6

1. Select * From #Temp_Loc  
2. Left Outer Join  
3. ##TEmp  
4. ON  
5. #Temp_Loc.Emp_ID=##TEmp.IID  

Output



This example illustrates how to use Joins on temp tables. We can use all types of joins (INNER, LEFT OUTER, RIGHT OUTER , CROSS JOIN , SELF JOIN) on temp tables.

Example 7
Let us assume we have a table.



This table contains duplicate data. Now we will use the temp table to remove the duplicate data.

1. /* Insert Data into temp Table */  
2.   
3. Select Distinct * into #Temp_tab  
4. from Employee_Detail  
5.   
6. /* Delete Data from Table */  
7. Delete from Employee_Detail  
8.   
9. /* Insert data from Temp table into Employee_Detail Table */  
10.   
11. insert into Employee_Detail  
12. select * from #Temp_tab  
13.   
14. /* Drop Temp Table */  
15. Drop Table #Temp_tab  
16.   
17. select * from Employee_Detail  

Output


This example illustrate that we can use temp tables for holding the data that we will use further in a query. In the preceding example we used a temp table for removing the duplicate data.

When to use temporary tables?

1. To hold data for further query.
2. When we have a complex joins operation.
3. In some cases it may be a replacement of cursors.