SQL Server Temporary tables,their types and use with examples

Introduction: In this article you will learn the following:

• What is temporary table in SQL Server?
• Types of temporary tables (local & global)
• Multiple ways to create temporary tables
• Use of temporary tables
• Performing Create, insert, update, delete operations on temporary tables
• Important points about temporary tables

What is temporary table in SQL Server?

Temporary tables are used to store a result set or we can say intermediate results into a temporary structure for processing. As we know in single variable we can only store single item. But sometimes we need to work on multiple items. So whenever we want to store multiple records for further usage or to perform calculations on them, we can use temporary table or table variable. 

Types of temporary tables:

Based on the scope and behavior, temporary tables are of two types:

1. Local temporary table
2. Global temporary table

 How to create a temporary table?

Local temporary tables:

The syntax to create a local temporary table is similar to the syntax of a normal table. CREATE TABLE statement is used to create a temporary table but the table name (Maximum 116 characters) is prefixed with ‘#’ (single hash sign). Its scope is limited to session in which it is created. These are automatically deleted once the session that created the table has been closed. We can also drop temporary table explicitly using drop command similar to normal table.

The existence of Local temp table is only to the current session of current user (current connection) or we can say to the current query window only. If we close the current query window where we created the local temporary table or open a new query window then it is not accessible and will give the error as: "Invalid object name".

One cannot create another local temporary table with the same name in the same session. It will give an error as: "There is already an object named '#temp' in the database"

but table with the same name can be created from another session. To test, open new query window in sql server management studio and create a local temporary table there with the same name as previously created local temporary table. It will create a new temporary table for that session also.

These tables are automatically destroyed at the end of the procedure or the session that created them.

Global temporary tables:

Global Temporary tables are visible to or available across all sessions and all users (all connections).The syntax to create a global temporary table is similar to the syntax of normal table. CREATE TABLE command is used to create a global temporary table but the table name (Maximum 116 characters) is prefixed with ‘##’ (double hash sign). Once created, it is available to all the users by any connection like a permanent table.

A Global Temporary table is dropped automatically once all connections using it have been closed.

How to create temporary tables?

As far as I know and tested, there are three ways to create a temporary table: 

1. Using CREATE TABLE command and inserting data into it similar to normal table
2. Using CREATE TABLE command and inserting data into it from other existing table using INSERT INTO- SELECT FROM command
3. Directly create and insert data into temporary table from other existing table using SELECT INTO- FROM command

Note: I am going to explain the creation of local temporary tables in the examples below. Similar is the way to create a global temporary table. We just need to use ## before global temporary table name instead of single # as we use in local temporary tables.

First Way: Create a temporary table and insert data into it like normal table.

--Create a temporary table

CREATE TABLE #tbBooks

(

                 BookId    INT NOT NULL IDENTITY(1,1),

                 BookName  VARCHAR(100),

                 Author    VARCHAR(100),

                 Publisher VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

--Insert data into temporary table

INSERT INTO #tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn MVC','Lalit','Lalit Publications',1600.00)

INSERT INTO #tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn ASP.NET','Neha','Neha Publications',1200.00)

INSERT INTO #tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn SQL','Shaurya','Shaurya Publications',1150.00)

INSERT INTO #tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn jquery','John','John Publications',1000.00)

INSERT INTO #tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn Javascript','Scott','Scott Publications',900.00)

--Check inserted data

SELECT * FROM #tbBooks

Output will be as:

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
4	Learn jquery	John	John Publications	1000.00
5	Learn Javascript	Scott	Scott Publications	900.00

Second Way: Using CREATE TABLE command and insert data into it from other existing table. To test, let’s first create a normal table and insert data into it.

--Create a normal table

CREATE TABLE tbBooks

(

                BookId                 INT PRIMARY KEY IDENTITY(1,1) NOT NULL,

                BookName         VARCHAR(100),

                Author                 VARCHAR(100),

                Publisher            VARCHAR(150),

                Price                     DECIMAL(10,2)

)

--Insert data into table

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn MVC','Lalit','Lalit Publications',1600.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn ASP.NET','Neha','Neha Publications',1200.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn SQL','Shaurya','Shaurya Publications',1150.00)

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn jquery','John','John Publications',1000.00) 

INSERT INTO tbBooks (BookName,Author,Publisher,Price) VALUES ('Learn Javascript','Scott','Scott Publications',900.00)

Now create a temporary table (#tbBooks) and insert data into it from the table (tbBooks) created above.

--drop existing #tbBooks temporary table(if already created)

DROP TABLE #tbBooks

--Create a temporary table

CREATE TABLE #tbBooks

(

                 BookId    INT NOT NULL IDENTITY(1,1),

                 BookName  VARCHAR(100),

                 Author    VARCHAR(100),

                 Publisher VARCHAR(100),

                 Price     DECIMAL(10,2)

 )

--Insert selected columns from existing table into temporary table.

 INSERT INTO #tbBooks SELECT BookName,Author,Publisher,Price  FROM tbBooks

--Check inserted data

 SELECT * FROM #tbBooks

Output will be:

BookName	Author	Publisher	Price
Learn MVC	Lalit	Lalit Publications	1600.00
Learn ASP.NET	Neha	Neha Publications	1200.00
Learn SQL	Shaurya	Shaurya Publications	1150.00
Learn jquery	John	John Publications	1000.00
Learn Javascript	Scott	Scott Publications	900.00

Third Way: Directly create and insert data into temporary table from other existing table using SELECT INTO- FROM command. This way we don’t need to create a temporary table explicitly. It automatically creates the temporary table and inserts data into it.

Now let’s create a temporary table automatically and insert all data into it from tbBooks table created above. Command will be:

--drop existing #tbBooks temporary table(if already created)

DROP TABLE #tbBooks

--Create temporary table automatically and copy all the data from existing table

SELECT * INTO #tbBooks FROM tbBooks

--Check inserted data

SELECT * FROM #tbBooks

Output will be:

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
4	Learn jquery	John	John Publications	1000.00
5	Learn Javascript	Scott	Scott Publications	900.00

Suppose we want to get all the records from tbBooks table where price is greater than 1000 and insert them into #tbBooks temporary table then command will be:

--drop existing #tbBooks temporary table(if already created)

DROP TABLE #tbBooks

--Create temporary table automatically and insert data into it from existing table based on condition

SELECT * INTO #tbBooks  FROM tbBooks WHERE Price>1000

--Check inserted data

SELECT * FROM #tbBook

Output will be as:

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00

Example demonstrating the use of temporary table to store and get all dates between two dates

DECLARE

 @StartDate AS DATE='2014-12-17';

DECLARE @EndDate AS DATE='2014-12-25';

CREATE TABLE #DateList(iDate DATE,iDayName VARCHAR(10))

WHILE (@StartDate<=@EndDate)

BEGIN

                INSERT #DateList(iDate,iDayName) VALUES(@StartDate,DATENAME(DW,@StartDate))

                SET @StartDate=CAST(DATEADD(DAY,1,@StartDate) AS DATE)

END

SELECT iDate AS [Date], iDayName AS [DayName] FROM #DateList

Output will be as:

In above example we are using temporary table to store and get all the dates and their day name between two dates.

Update & Delete records in temporary table

We can perform update and delete operation in temporary table as we do in normal table

Update in temporary table:  Update operation on temporary table is similar to normal table

--drop existing #tbBooks temporary table(if already created)

DROP TABLE #tbBooks

--Create temporary table automatically and copy all the data from existing table

SELECT * INTO #tbBooks FROM tbBooks

--Update the record

UPDATE #tbBooks  SET Price=1100 WHERE BookId=4;

--Check data in table after updation

SELECT * FROM #tbBooks

Output will be: 

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
4	Learn jquery	John	John Publications	1100.00
5	Learn Javascript	Scott	Scott Publications	900.00

Note: The price of Book with Id=4 is updated to 1100 from 1000 in the temporary table

  

Delete from temporary table: Delete operation in temporary table is similar to normal table

--drop existing #tbBooks temporary table(if already created)

DROP TABLE #tbBooks

--Create temporary table automatically and copy all the data from existing table

SELECT * INTO #tbBooks FROM tbBooks

--Delete the record

DELETE FROM #tbBooks WHERE BookId=4;

--Check data in table after deleting the record

SELECT * FROM #tbBooks

BookId	BookName	Author	Publisher	Price
1	Learn MVC	Lalit	Lalit Publications	1600.00
2	Learn ASP.NET	Neha	Neha Publications	1200.00
3	Learn SQL	Shaurya	Shaurya Publications	1150.00
5	Learn Javascript	Scott	Scott Publications	900.00

Note: Record with the BookId=4 is deleted from the temporary table

Important points about temporary tables 

• Temporary tables are alternative of table variables to store multiple result set.
• Temporary tables can be defined as local or global 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 end of the procedure or session that created them.
• Global temporary tables are temporary tables that are available to all sessions and all the users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
• Temporary tables can be used in Stored Procedures, Triggers and Batches but not in user defined functions
• Temporary tables can be access in nested stored procedures
• Temporary Table can be truncated like normal table
• Temporary Table can be altered like normal table
• Temporary tables used in stored procedures cause more recompilations of the stored procedures than when table variables are used.
• The data in the temporary table will  be rolled back when a transaction is rolled back similar to normal table
• A temporary table will generally use more  resources than its counterpart table variable
• PRIMARY KEY, UNIQUE, NULL, CHECK etc can be implemented at the time of creating temporary tables using CREATE TABLE statement or can be added after the table has been created.
• FOREIGN KEY is not allowed in temporary table.
• Temporary tables can be indexed even after creation.

Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments. Stay tuned and stay connected for more technical updates."