SQL Server Temporary Tables

Summary: in this tutorial, you will learn how to create SQL Server temporary tables and how to manipulate them effectively.

Temporary tables are tables that exist temporarily on the SQL Server.

The temporary tables are useful for storing the immediate result sets that are accessed multiple times.

Creating temporary tables

SQL Server provided two ways to create temporary tables via SELECT INTO and CREATE TABLE statements.

Create temporary tables using SELECT INTO statement

The first way to create a temporary table is to use the SELECT INTO statement as shown below:

SELECT 
    select_list
INTO 
    temporary_table
FROM 
    table_name
....
Code language: SQL (Structured Query Language) (sql)

The name of the temporary table starts with a hash symbol (#). For example, the following statement creates a temporary table using the SELECT INTO statement:

SELECT
    product_name,
    list_price
INTO #trek_products --- temporary table
FROM
    production.products
WHERE
    brand_id = 9;
Code language: SQL (Structured Query Language) (sql)

In this example, we created a temporary table named #trek_products with two columns derived from the select list of the SELECT statement. The statement created the temporary table and populated data from the production.products table into the temporary table.

Once you execute the statement, you can find the temporary table name created in the system database named tempdb, which can be accessed via the SQL Server Management Studio using the following path System Databases > tempdb > Temporary Tables as shown in the following picture:

As you can see clearly from the picture, the temporary table also consists of a sequence of numbers as a postfix. This is a unique identifier for the temporary table. Because multiple database connections can create temporary tables with the same name, SQL Server automatically appends this unique number at the end of the temporary table name to differentiate between the temporary tables.

Create temporary tables using CREATE TABLE statement

The second way to create a temporary table is to use the CREATE TABLE statement:

CREATE TABLE #haro_products (
    product_name VARCHAR(MAX),
    list_price DEC(10,2)
);
Code language: SQL (Structured Query Language) (sql)

This statement has the same syntax as creating a regular table. However, the name of the temporary table starts with a hash symbol (#)

After creating the temporary table, you can insert data into this table as a regular table:

INSERT INTO #haro_products
SELECT
    product_name,
    list_price
FROM 
    production.products
WHERE
    brand_id = 2;
Code language: SQL (Structured Query Language) (sql)

Of course, you can query data against it within the current session:

SELECT
    *
FROM
    #haro_products;
Code language: SQL (Structured Query Language) (sql)

However, if you open another connection and try the query above query, you will get the following error:

Invalid object name '#haro_products'.
Code language: SQL (Structured Query Language) (sql)

This is because the temporary tables are only accessible within the session that created them.

Global temporary tables

Sometimes, you may want to create a temporary table that is accessible across connections. In this case, you can use global temporary tables.

Unlike a temporary table, the name of a global temporary table starts with a double hash symbol (##).

The following statements first create a global temporary table named ##heller_products and then populate data from the production.products table into this table:

CREATE TABLE ##heller_products (
    product_name VARCHAR(MAX),
    list_price DEC(10,2)
);

INSERT INTO ##heller_products
SELECT
    product_name,
    list_price
FROM 
    production.products
WHERE
    brand_id = 3;
Code language: SQL (Structured Query Language) (sql)

Now, you can access the ##heller_products table from any session.

Dropping temporary tables

Automatic removal

SQL Server drops a temporary table automatically when you close the connection that created it.

SQL Server drops a global temporary table once the connection that created it closed and the queries against this table from other connections completes.

Manual Deletion

From the connection in which the temporary table created, you can manually remove the temporary table by using the DROP TABLE statement:

DROP TABLE ##table_name;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about SQL Server temporary tables and how to create and remove them effectively.