Temporary tables are a useful feature in SQL that allows you to store and manipulate data temporarily during the execution of a query or a stored procedure. In this tutorial, we will focus on how to insert data into a temporary table from an existing physical table.
Creating a Temporary Table
Before inserting data into a temporary table, you need to create it first. A temporary table is created using the CREATE TABLE
statement with a table name that starts with a number sign (#). For example:
CREATE TABLE #TempTable(
ID int,
Date datetime,
Name char(20)
)
This creates a temporary table named #TempTable
with three columns: ID
, Date
, and Name
.
Inserting Data into a Temporary Table
To insert data into a temporary table, you can use the INSERT INTO
statement followed by a SELECT
statement that retrieves data from an existing physical table. The syntax is as follows:
INSERT INTO #TempTable (column1, column2, ...)
SELECT column1, column2, ...
FROM physical_table
For example:
INSERT INTO #TempTable (ID, Date, Name)
SELECT id, date, name
FROM physical_table
This inserts the values from the id
, date
, and name
columns of the physical_table
into the corresponding columns of the #TempTable
.
Alternative Method: Using SELECT INTO
Another way to insert data into a temporary table is by using the SELECT INTO
statement. This method creates a new temporary table with the same structure as the physical table and inserts the data into it. The syntax is as follows:
SELECT * INTO #TempTable
FROM physical_table
For example:
SELECT * INTO #TempTable
FROM OriginalTable
Note that this method creates a new temporary table with the same structure as the physical table, so you don’t need to create the temporary table beforehand.
Dropping a Temporary Table
After you have finished using a temporary table, it’s a good practice to drop it to free up resources. You can use the DROP TABLE
statement to drop a temporary table:
DROP TABLE #TempTable
It’s also a good idea to check if the temporary table exists before dropping it to avoid errors.
Checking if a Temporary Table Exists
You can use the OBJECT_ID
function to check if a temporary table exists:
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
DROP TABLE #MyTable
This checks if the temporary table #MyTable
exists in the tempdb
database, and if it does, drops it.
Conclusion
In this tutorial, we have learned how to insert data into a temporary table from an existing physical table using the INSERT INTO
statement and the SELECT INTO
statement. We have also learned how to drop a temporary table and check if it exists before dropping it. Temporary tables are a powerful feature in SQL that can be used to simplify complex queries and improve performance.