How to Create a Temporary Table in SQL Server

Here are two approaches to create a temporary table in SQL Server:

(1) The SELECT INTO approach:

SELECT column_1, column_2, column_3,...  
INTO #name_of_temp_table
FROM table_name
WHERE condition

(2) The CREATE TABLE approach:

CREATE TABLE #name_of_temp_table (
	column_1 datatype,
	column_2 datatype,
	column_3 datatype,
        .
        .
        
	column_n datatype
)

Note that once you created the table under the second approach, you’ll need to insert the records into the table using an INSERT INTO query:

INSERT INTO #name_of_temp_table (column_1, column_2, column_3,...) 
SELECT column_1, column_2, column_3,...
FROM table_name
WHERE condition

Under both approaches, you must include the hash symbol (#) before the temporary table name.

You can drop a temporary table using the DROP TABLE query (or simply by closing the connection that was used to create the temporary table):

DROP TABLE #name_of_temp_table

In the next section, you’ll see how to create a temporary table in SQL Server using the two approaches described above. You’ll also learn how to create a global temporary table.

(1) Create a Temporary Table using the SELECT INTO Approach

In the example below, you’ll see how to create a temporary table from an existing table called products.

The current ‘products’ table contains the following columns and data:

The ultimate goal is to create a temporary table (from the ‘products’ table) for all the records where the price is greater than 300.

You can then create a temporary table (called #products_temp_table) using the SELECT INTO Approach:

SELECT product_id, product_name, price 
INTO #products_temp_table
FROM products
WHERE price > 300

After running the query, you’ll notice that 3 rows were affected:

(3 rows affected)

You can check the content of the temporary table by running the following SELECT query:

SELECT * FROM #products_temp_table

As you can see, there are currently 3 rows in the table where the price is greater than 300:

You can drop the temporary table using the DROP TABLE query:

DROP TABLE #products_temp_table

After dropping the table, try to run the SELECT query again:

SELECT * FROM #products_temp_table

Notice that the table no longer exists:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#products_temp_table'.

(2) Create a Temporary Table using the CREATE TABLE Approach

Now recreate the temporary table using the second CREATE TABLE approach:

CREATE TABLE #products_temp_table (
	product_id int primary key,
	product_name nvarchar(50),
	price int
)

Once you created the temporary table, you’ll need to insert the records into the table using the INSERT INTO query:

INSERT INTO #products_temp_table (product_id, product_name, price) 
SELECT product_id, product_name, price
FROM products
WHERE price > 300

You’ll notice that 3 records were affected:

(3 rows affected)

Rerun the SELECT query in order to check the content of the table:

SELECT * FROM #products_temp_table

As you can see, there are 3 records where the price is greater than 300:

To drop the table use:

DROP TABLE #products_temp_table

Create a Global Temporary Table in SQL Server

You can also create a global temporary table by placing double hash (##) before the temporary table name.

The global temporary table will be available across different connections.

Here is the query to create the global temporary table using the SELECT INTO approach:

SELECT product_id, product_name, price
INTO ##products_temp_table
FROM products
WHERE price > 300

3 records will be affected:

(3 rows affected)

You can then run the following SELECT query:

SELECT * FROM ##products_temp_table

You’ll get the same 3 records where the price is greater than 300:

To drop the table use:

DROP TABLE ##products_temp_table

Optionally, you can use the CREATE TABLE approach in order to create the global temporary table:

CREATE TABLE ##products_temp_table (
	product_id int primary key,
	product_name nvarchar(50),
	price int
)

Then, run the INSERT INTO query to insert the records into the table:

INSERT INTO ##products_temp_table (product_id, product_name, price) 
SELECT product_id, product_name, price
FROM products
WHERE price > 300

3 records will be inserted into the table.

Rerun the SELECT query:

SELECT * FROM ##products_temp_table

As before, you’ll see the 3 records where the price is greater than 300: