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.

Example

CREATE TABLE #TEMP_SAVE
(
   Id INT,
   Name VARCHAR(30),
   Date DATETIME DEFAULT GETDATE()
)

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

CREATE TABLE ##TEMP_SAVE
(
   Id INT,
   Name VARCHAR(30),
   Date DATETIME DEFAULT GETDATE()
)

Now we will examine some examples of temp tables.

Example 1 Create a local temp table.

CREATE TABLE #TEMP_SAVE
(
   Id INT,
   Name VARCHAR(30),
   Age INT
)

Now insert data into the local table.

INSERT INTO #TEMP_SAVE
SELECT 1, 'pankaj', 20 UNION ALL
SELECT 2, 'Rahul', 21 UNION ALL
SELECT 3, 'Sandeep', 22 UNION ALL
SELECT 4, 'Sanjeev', 23 UNION ALL
SELECT 5, 'Neeraj', 24

Fetch data from the local temp table.

SELECT * FROM #TEMP_SAVE

Drop the local temp table.

DROP TABLE #TEMP_SAVE

Example 2 Create a global temp table.

CREATE TABLE ##TEmp
(
   IID int identity(1,1),
   Name nvarchar(50),
   Salary int
)

Insert data into the global temp table.

INSERT INTO ##Temp
SELECT 'Pankaj', 25000 UNION ALL
SELECT 'Rahul', 24000 UNION ALL
SELECT 'Sanjeev', 23000 UNION ALL
SELECT 'Sandeep', 22000 UNION ALL
SELECT 'Naru', 27000

Fetch data the from global temp table.

SELECT * FROM ##Temp

Drop the temp table.

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.

SELECT * INTO #Temp_Loc FROM Employee
SELECT * FROM #Temp_Loc

Output

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

Output

CREATE VIEW My_View
AS
SELECT * FROM #Temp_Loc
WHERE Emp_Id > 3

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

SELECT * FROM ##Temp
UNION ALL
SELECT * FROM #TEMP_SAVE

Output

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

Example 6 

SELECT *
FROM #Temp_Loc
LEFT OUTER JOIN ##TEmp ON #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.

-- Insert Data into temp Table
SELECT DISTINCT * INTO #Temp_tab FROM Employee_Detail;

-- Delete Data from Table
DELETE FROM Employee_Detail;

-- Insert data from Temp table into Employee_Detail Table
INSERT INTO Employee_Detail
SELECT * FROM #Temp_tab;

-- Drop Temp Table
DROP TABLE #Temp_tab;

-- Select data from Employee_Detail Table
SELECT * FROM Employee_Detail;

This example illustrates 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, a global temporary table and how to fetch the data.