Working with Temporary Tables in SQL: Inserting Data

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.