What is a Temporary Table in SQL?

Local temporary tables#

Local temporary tables are created using the CREATE TABLE statement with a single hash sign (#) before the table name. They are only available in the current user session and automatically dropped when the session that created them is closed. An example of creating a local temporary table is as follows:

CREATE TABLE #TempTable (ID INT, Name VARCHAR(50));

The above statement creates a local 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. We can use the regular insert statements to insert the data into the local temporary table. The example is given below:

INSERT INTO #TempTable (ID, Name) VALUES (1, 'Educative'), (2, 'DevPath');

In the above example, notice that we created a temporary table using the CREATE statement and then inserted the data using the INSERT statement. 

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

SELECT * FROM #TempTable;

The output of the above commands is shown below:

The above example shows what is a temporary table in SQL. There is another way of creating and inserting data into a temporary table, and that is using the INTO statement. To understand this method, let’s suppose we have a sales table with columns SaleID, ProductID, SalesAmount, and SaleDate as follows:

In this example, suppose we want to create a temporary table using the Sales tables for ProductID and SalesAmount only. The following syntax shows a second way of creating and inserting the data into a temporary table using the INTO statement:

SELECT ProductID, SalesAmount INTO #TempTable FROM Sales

In the above example, we create a temporary table named #TempTable with two columns, ProductID and SalesAmount. These columns are derived from the select list of the SELECT statement. The above query creates the temporary table and inserts the data from the Sales table.

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

DROP TABLE #TempTable;

An example#

In this example, we will create a local temporary table to hold total sales with respect to the product. For this purpose, we create a temporary table with two columns. The first column represents the product's ID, and the second column represents the total amount of sales for that particular product. First, let's create a temporary table as follows:

CREATE TABLE #TempResults (ProductID INT, TotalSales DECIMAL(10, 2));

Once the temporary table is created, we then use a query to populate it. Here, we assume that the database has a table named Sales that contains the products along with the sales amount. The following is the sample Sales table, along with the queries to create and populate it:

Sales table

Queries

The following is the query to insert data into the temporary table from the Sales table:

INSERT INTO #TempResults (ProductID, TotalSales)

SELECT ProductID, SUM(SalesAmount)

FROM Sales

GROUP BY ProductID;

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:

SELECT ProductID, TotalSales

FROM #TempResults

WHERE TotalSales > 1000;

The output is given as follows:

The above example shows the use of storing intermediate results in the temporary table. In the next section, we will discuss global temporary tables.

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. An example of creating a global temporary table is as follows:

CREATE TABLE ##TempTable (ID INT, Name VARCHAR(50));

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. We can use the regular insert statements to insert the data into the global temporary table. An example is given below:

INSERT INTO ##TempTable (ID, Name) VALUES (1, 'Educative'), (2, 'DevPath');

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

SELECT * FROM ##TempTable;

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

DROP TABLE ##TempTable;

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:

In conclusion, temporary tables are helpful when working with large data and complex queries. They are helpful in storing intermediate data within a specific session or transaction context.

We hope this blog has helped you understand what is a temporary table in SQL and its use. To learn more about SQL, you may find the following courses helpful:

• An Introductory Guide to SQL

• Practical Data Analysis with SQL