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 in SQL Server 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:
| product_id | product_name | price |
| 1 | Laptop | 1200 |
| 2 | Printer | 200 |
| 3 | Tablet | 350 |
| 4 | Keyboard | 80 |
| 5 | Monitor | 400 |
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:
| product_id | product_name | price |
| 1 | Laptop | 1200 |
| 3 | Tablet | 350 |
| 5 | Monitor | 400 |
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 in SQL Server Using the CREATE TABLE Approach
Let’s 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:
| product_id | product_name | price |
| 1 | Laptop | 1200 |
| 3 | Tablet | 350 |
| 5 | Monitor | 400 |
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:
| product_id | product_name | price |
| 1 | Laptop | 1200 |
| 3 | Tablet | 350 |
| 5 | Monitor | 400 |
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:
| product_id | product_name | price |
| 1 | Laptop | 1200 |
| 3 | Tablet | 350 |
| 5 | Monitor | 400 |