What is a temporary table in SQL?

Handling complex SQL queries can often be challenging, especially when multiple steps or intermediate results are involved. Temporary tables in SQL offer an effective solution for managing such scenarios. SQL temporary tables store temporary data, typically during a database session or transaction. They are invaluable for holding intermediate results generated during complex queries and calculations. For this reason, they simplify multistep queries, making data processing easier to manage and organize. Like regular tables, you can perform CRUD (create, read, update, delete) operations and join operations on temporary tables. These are automatically dropped when the session ends, but users can also drop them explicitly if needed.

Temporary tables are supported across various database systems, including MySQL, Oracle, and SQL Server. However, the features and syntax for temporary tables can vary depending on the database server.

In this blog, we’ll focus on SQL Server’s implementation of temporary tables, including the two types it supports:

• Local temporary tables

• Global temporary tables

Local temporary tables#

A local temporary table in SQL Server is created for a single session and is automatically dropped when the session ends. It is identified by a single hash (#) before the table name and is available only to the session that created it. These tables are automatically deleted when the session ends.

Creating a local temporary table#

Local temporary tables are created using the CREATE TABLE statement with a single hash sign (#) before the table name. Here’s how:

In this example, the statement creates a local temporary table named #TempTable with two columns: ID (of type INT) and Name (of type VARCHAR(50)).

Inserting data into a local temporary table#

Once the local temporary table is created, we can insert data using regular INSERT statements. Here’s an example:

We inserted two rows using the INSERT statement into the temporary table.

Querying data from the local temporary table#

Once we’ve created and inserted data into a temporary table, we can use it in queries like a regular table. For example, to get the data from a temporary table, we can use the SELECT statement as follows:

The output of the above commands is shown below:

The above illustration shows the data stored in the temporary table.

Creating temporary tables with the INTO statement#

Another way to create and insert data into a temporary table is by using the INTO statement.

To understand this method, let’s suppose we have a Sales table with columns SaleID, ProductID, SalesAmount, and SaleDate, as displayed below:

You can use the following queries to create the Sales table:

To create a temporary table containing only ProductID and SalesAmount from the Sales table using the INTO statement, you can use the following syntax:

This query creates a temporary table #TempTable with the columns ProductID and SalesAmount, and populates it with the data from the Sales table.

Dropping a local temporary table#

To explicitly drop a local temporary table when it’s no longer needed, you can use the following command:

Example: Using local temporary tables for the Sales data#

In this example, we will create a local temporary table to hold total sales by product. The table will contain two columns: ProductID and TotalSales.

Step 1: Create the temporary table#

First, let’s create a temporary table to store the product ID and total sales:

Step 2: Populate the temporary table#

Once the temporary table #TempResults is created, we then use a query like the following to populate it with the Sales table:

This query will insert the total sales per product into the #TempResults table.

Step 3: Query the data#

Once data is inserted into the temporary table, we can use this table in further calculations or queries. For example, to retrieve products where the sale amount is greater than 1000, we can use the following query:

This query will return the products that meet the criteria of having sales amounts greater than $1000$, as displayed in the output given below:

Till now, we have seen how local temporary tables are created, populated, and used within a session. These tables simplify complex queries by breaking them into smaller, more manageable steps. Now that we’ve explored local temporary tables, let’s look at their global counterparts.

Global temporary tables#

Global temporary tables are created using the CREATE TABLE statement with the double hash sign (##) before the table name. They are available to all user sessions within a given database and are dropped when the last session using them is closed.

Creating a global temporary table#

An example of creating a global temporary table is as follows:

The above statement creates a global temporary table called ##TempTable with two columns, ID and Name. The ID column is of type INT, and the Name column is of type VARCHAR, with a limit of 50 characters.

Inserting data into a global temporary table#

We can use the regular insert statements to insert the data into the global temporary table. An example is given below:

Querying data from the global temporary table#

Once we create and insert data into a temporary table, we can use it in queries like a regular table. For example, to get the data from a temporary table, we can use the SELECT statement as follows:

Dropping a global temporary table#

The following is the syntax to explicitly drop the local temporary table:

Example: Using global temporary tables for the Sales data#

Global temporary tables are useful when multiple sessions or users need to access the same temporary data.

Step 1: Create the global temporary table#

First, we create a global temporary table to hold the total sales by product:

This creates a table named ##GlobalTempResults with two columns: ProductID and TotalSales.

Step 2: Populate the global temporary table#

The next step is to populate the table using data from the Sales table. Assume the Sales table is already created and contains the following data:

Sales Table:

Populate the global temporary table with total sales per product:

This query calculates the total sales for each product and stores the results in ##GlobalTempResults.

Step 3: Query the global temporary table#

As global temporary tables are accessible across sessions, another user session can now query the data. For example, retrieve products where total sales exceed 1000:

Output:

Step 4: Drop the global temporary table#

When the table is no longer needed, you can drop it explicitly:

This ensures that the global temporary table is removed from the database.

Comparison of local and global temporary tables#

The usage of global temporary tables is similar to the local temporary table. However, there are slight differences in both, which are presented in the following table:

What you’ve learned#

Temporary tables are a powerful tool for managing large datasets and simplifying complex queries. They allow for storing intermediate results within a session or transaction, making data manipulation more efficient.

Here’s a quick recap of what you’ve learned:

• SQL temporary tables store short-term data needed within a session or transaction. They help break down complex queries into manageable steps.

• There are two types of SQL temporary tables:

  • Local temporary tables: Used only within the session that created them and deleted automatically when the session ends.

  • Global temporary tables: Shared across all sessions and deleted when the last session using them closes.

• Database support: SQL Server, MySQL, and Oracle support temporary tables, though syntax and features vary.

• Creation methods in SQL Server: Temporary tables can be created in two ways in SQL Server:

  • Using CREATE TABLE with a single # for local or ## for global tables.

  • Using the SELECT INTO statement to create a local temporary table and copy data directly into it.

• Usage: Temporary tables function like regular tables for CRUD operations and joins.

• Cleanup: Local tables are automatically removed when the session ends, but both types can be explicitly dropped with DROP TABLE.

Want to level up your SQL skills? Learn how to design and manipulate databases, write advanced queries, and confidently prepare for SQL interviews with the course below.