Temporary Tables in SQL Server

In this article we will learn about Temporary Tables in SQL Server.

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.

tempdb table

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  
id

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  
Fetch Data From Global temp Table

Drop the temp table.
  1. Drop Table ##TEmp  
Example 3

We have an Employee table that look like the following:

Employee table

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

Select Into command

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

use Union

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

Joins on Temp tables

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.

contain duplicate data

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

data that will we use further in query

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.

So in this article we saw how to create a temporary table, global temporary table and how to fetch the data.