Sql server Temporary tables,their types and examples to use

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. In my last detailed article i explained about Table variable and its use with examples

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:
Use of temporary table to store and get all dates between two dates

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

1.       Temporary tables are alternative of table variables to store multiple result set.
2.       Temporary tables can be defined as local or global temporary tables. 
3.       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. 
4.       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. 
5.       Temporary tables can be used in Stored Procedures, Triggers and Batches but not in user defined functions 
6.       Temporary tables can be access in nested stored procedures.
7.       Temporary Table can be truncated like normal table.
8.       Temporary Table can be altered like normal table.
9.       Temporary tables used in stored procedures cause more recompilations of the stored procedures than when table variables are used. 
10.   The data in the temporary table will be rolled back when a transaction is rolled back similar to normal table.
11.   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. 
12.   FOREIGN KEY is not allowed in temporary table. 

13.   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, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

5 comments

Click here for comments
Unknown
admin
February 01, 2015 × This comment has been removed by a blog administrator.
avatar
Unknown
admin
December 05, 2015 ×

Thanks for such explanatory tutorial

Reply
avatar
December 12, 2015 ×

Thanks dhananjay for your valuable comments. Stay connected and keep reading for more useful updates..:)

Reply
avatar
Swapnil
admin
October 03, 2018 ×

Nice article

Reply
avatar
Harish
admin
September 15, 2019 ×

Nice Explanation, Reader friendly

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..