MS SQL Server

SQL Server Insert into Temp Table

Temporary tables, or temp tables, refer to tables created by a SQL Server user for the sole purpose of storing data temporarily. Temp tables are useful for working with subsets of data that would require mounds of specified queries to filter.

This guide will discuss how to work with temporary tables in SQL Server. We will start at the basics and learn how to create temp tables, use global temporary tables, insert data into temporary tables, and drop temp tables.

SQL Server Create Temp Tables

In SQL Server, there are two primary methods for creating temp tables:

  1. Using SQL SELECT statement.
  2. Using the CREATE TABLE statement. 

Let us look at each of the methods above.

Using SQL Select Statement

You can use the SELECT INTO statement to create a temporary table and insert data from a defined query.

The syntax for creating a temp table with the select statement is as shown:

SELECT column_list INTO #temporary_table_name FROM TABLE_NAME WHERE conditional_expression;

We use the select statement followed by the name of the temporary table. The name of a temp table in SQL Server starts with a # sign.

Consider the example below that creates a temp table using various columns of an existing table:

USE salesdb;
SELECT *  INTO #sales_temp FROM Sales WHERE Quantity > 5;

The above query should select the matching records and insert them into the specified temporary table.

SQL Server stores temp tables in the tempdb database. This is a system database created automatically by SQL Server.

In SQL Server Management Studio, you can view the temporary table created above by navigating: Databases –> System Databases –> tempdb –> Temporary Tables:

Each temporary table contains a postfix unique identifier, including a sequence of numerical values. This is because multiple connections can create temporary tables with similar names. SQL Server appends a unique numerical value at the end of the name to avoid conflicts.

Using the Create Table Query

The second method we can use to create a temporary table is the SQL CREATE TABLE statement. This method is not very different from that of a normal table. However, the table name starts with a pound # sign.

For example:

CREATE TABLE #my_temp_table (
        id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        name VARCHAR(50)
);

The above query will create a temporary table with the specified name.

Once we have the temp table created, we can insert data as a normal table as shown in the query below:

USE tempdb;
INSERT INTO #my_temp_table(name)
VALUES ('MySQL'),
           ('PostgreSQL'),
           ('MongoDB'),
           ('SQLite');

If you need to get the records stored in the temp database, you can use the select statement as shown:

SELECT * FROM #my_temp_table;

Example output is as shown:

SQL Server Drop Temp Tables

Once you have created a temp table, you will probably want to delete it after use. In SQL Server, there are two methods of dropping a temporary table:

Terminate Connection

SQL Server will auto-delete all temporary tables when the connection that created them is ended.

As mentioned, a temp table is only available in the connection that creates it. Hence, once the connection is closed, the SQL server deletes the tables and frees up the resources for other connections.

Drop Statement

The second method you can use to delete a temp table is the SQL DROP query. For example, to delete the my_temp_table created in the previous queries:

DROP TABLE #my_temp_table;

Global Temp Tables

A temp table is only available to the connection created by default. However, you can create a table available in all connections on the server. These are known as global temporary tables.

To create a global temp in SQL Server, use double pound signs (##).

For example:

CREATE TABLE ##my_temp_table (
        id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
        name VARCHAR(50)
);
USE tempdb;
INSERT INTO ##my_temp_table(name)
VALUES ('MySQL'),
           ('PostgreSQL'),
           ('MongoDB'),
           ('SQLite');

Unlike a single session table, the SQL server drops global temp tables after the created connection is closed, and all other connections are closed.

In Closing

In this article, you understood how to work with the temporary tables in SQL Server. Temp tables can be beneficial when used efficiently.

Thank you for reading!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list