Introduction to Temporary Tables in SQL Server

Total: 192 Average: 4.2

A temporary table in SQL Server, as the name suggests, is a database table that exists on the database server temporarily. It stores a subset of the normal table data for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and need to interact with small subsets of those records constantly. In such cases, instead of filtering the data again and again to fetch the subset, you can filter the data once, and store it in a temporary table. Then, you can execute queries on that temporary table.

Temporary tables are stored inside “tempdb,” which is a system database. Now, let’s take a look at how you can use temporary data in a simple scenario.

Preparing the Data

First, we prepare some dummy data. We use them to create SQL Server temporary tables.

Run the following script on your database server:


    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    total_score INT NOT NULL,

INSERT INTO student 

VALUES (1, 'Jolly', 'Female', 20, 500), 
(2, 'Jon', 'Male', 22, 545), 
(3, 'Sara', 'Female', 25, 600), 
(4, 'Laura', 'Female', 18, 400), 
(5, 'Alan', 'Male', 20, 500), 
(6, 'Kate', 'Female', 22, 500), 
(7, 'Joseph', 'Male', 18, 643), 
(8, 'Mice', 'Male', 23, 543), 
(9, 'Wise', 'Male', 21, 499), 
(10, 'Elis', 'Female', 27, 400);

The above SQL script creates a database called ‘schooldb’. In this database, a table called ‘student’ is created, and some dummy data added to the table.

Creating A Temporary Table

There are two methods for creating SQL Server temporary tables.

Creating a Temporary Table using SELECT INTO statement

The simplest way of creating a temporary table is by using an INTO statement within a SELECT query.

Let’s create a temporary table that contains the name, age, and gender of all male students’ records from the student table.

USE schooldb;

SELECT name, age, gender
INTO #MaleStudents
FROM student
WHERE gender = 'Male'

Take a look at the above query. We created a temporary table “#MaleStudents,” which stores names, ages, and genders of all male students’ records from the student table. To define a temporary table, we use the INTO statement after the SELECT statement. The name of a temporary table must start with a hash (#).

Now, we want to view the table location. We go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”.

You will see your temporary table name along with the identifier. Take a look at the following figure:

SQL Server temporary table in Object Explorer, created by using an INTO statement within a SELECT query

You must be wondering about the “000000000006” at the end of the table name. It is a unique identifier. Multiple database connections can create temporary tables with the same name. Therefore, to differentiate between the temporary tables created this way, the database server automatically appends this unique identifier at the end.

You can perform operations on the temporary table via the same connection that created it. In the same query window that created the “#MaleStudents” table, execute the following query:

SELECT * FROM #MaleStudents

The #MaleStudents table contains names, ages, and genders of all male students. The above query will fetch the following results:


To create a new connection, simply open a new query window in “SQL Server Management Studio”. Then, keep the previous connection open and create another “MaleStudents” table, using the second method in a new query window (new connection). We explore this second method below.

Creating a Temporary Table using CREATE TABLE Command

The second method is similar to creating normal tables. Take a look at the following query. Again, we create the #MaleStudents temporary table. Remember, this query must be executed by a new connection.

USE schooldb;

CREATE TABLE #MaleStudents
	name VARCHAR(50),
	age int,
	gender VARCHAR (50)


INSERT INTO #MaleStudents
SELECT name, age, gender
FROM student
WHERE gender = 'Male'

Now, if you execute the above query, you should see two #MaleStudents temporary tables with different unique identifiers inside the tempdb. This is because these two tables have been created by two different connections. Take a look at the following screenshot:

Temporary table in Object Explorer, created by using CREATE TABLE Statement

Temporary tables can be of different types. The most frequently mentioned are Global temporary tables and Local temporary tables.

Global Temporary Tables

It is pertinent to mention here that a temporary table is only accessible to the connection that created that temporary table. It is not accessible to other connections. However, we can create temporary tables that are accessible to all the open connections. Such temporary tables are called global temporary tables. The name of the global temporary table starts with a double hash symbol (##).

Let’s create a global temporary table that contains records of all female students from the student table.

USE schooldb;

SELECT name, age, gender
INTO ##FemaleStudents
FROM student
WHERE gender = 'Female'

Now, you can access the ##FemaleStudents table from any of the open connections.

Local temporary tables

The names of these tables begin with #. The full length of this name must be shorter than 116 symbols. This type is more secure than “global,” as it is only available for the owning process. It is impossible to use them in views, and triggers won’t get associated with the Local temporary tables. When the session or procedure finishes, the Local temporary table is dropped.

Dropping a temporary table

There are two ways to delete temporary tables in SQL Server: Automatic Deletion and Manual Deletion.

Automatic Deletion

A temporary table is automatically deleted when the connection that created the table is closed. Alternatively, when you close the query window that created the temporary table. Without saving changes, the table will be closed. If a connection is executing some queries on the global table, those queries must be completed before the global table deletion.

Manual Table Deletion

You can manually delete a table without closing a connection. For that, use the DROP TABLE statement. However, remember that the statement has to be executed by the connection that actually created the table.

Take a look at the following query:

DROP TABLE #MaleStudents

This is similar to deleting a normal table.

Using Temporary Tables in Stored Procedure

Earlier we learned that a temporary table can only be accessed locally inside the connection that created it. There is one exception to that rule. When you are creating stored procedures, you can access temporary tables in other connections as well.

Let’s create two stored procedures using two different connections. The first stored procedure will insert the data into the #MaleStudents table, while the second one will select the data from the table.

Create a new connection. If you are using SQL Server Management Studio, you can do this by opening a new query window. Execute the following SQL script in the new query window:

Create Procedure spInsertStudent
    (@Name Varchar(50), @Age int,  @Gender Varchar(50))
    Insert Into #MaleStudents
    Values (@Name, @Age, @Gender)

We’ve now created a stored procedure that inserts a record into the #MaleStudent temporary table. Note that this connection did not create the #MaleStudent, yet we are accessing it by inserting a record into it. The reason is, creating a stored procedure lets you access temporary tables from a connection other than the one that created the table. If you execute the above query, you will see that SQL Server will not throw any error.

Similarly, open a new connection and create the following stored procedure in it:


	SELECT * FROM #MaleStudents
	ORDER BY name

The above-stored procedure selects all the records from the #MaleStudents temporary table. Here again, we are accessing a temporary table from inside the connection that did not create the table.

Now, the tricky part comes. Though you can access a temporary table inside another connection when creating a stored procedure, you cannot access a temporary table when “executing” a stored procedure inside another connection. To execute a stored procedure that accesses a temporary table, you have to be inside the connection that created the temporary table.

Therefore, execute the following queries inside the connection that created the #MaleStudents table:

EXECUTE spInsertStudent Bradley, 45, Male
Execute spListStudent

The first stored procedure inserts a new student record with the name: Bradley, age: 45, and gender: Male into the #MaleStudents table.

The second stored procedure selects all the records from the #MaleStudents table in the ascending order of name. The output of the above-stored procedures will be:


You can clearly see our newly inserted record in the selected records above.

See Also:

Performance of Table Variables in SQL Server

Introducing Common Table Expressions in SQL Server

Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training. Acuity is an IT training business offering classroom courses in London and Guildford. It is a leading provider of SQL training the UK and offers a full range of SQL training from introductory training to advanced administration courses.