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: