SQL Temp Tables: The Ultimate Guide

Have you ever heard of an SQL temp table or a temporary table? Wondering what it is? Learn all about them in this guide.

What is an SQL Temp Table?

A temp table or temporary table in SQL is a table that exists temporarily on your database.

They only exist for a short time (e.g. the current session). They are useful for storing data that you work with multiple times in a session but the data is not needed permanently.

If you’re working with a set of data in your session and you find you need to query a table or a set of tables in the same way more than once, you might consider using temporary tables.

Instead of running the same query on the source tables (which may be time-consuming), you could load the results of your query into a temp table, then query the temp table when you need the data. This could improve the run time of your query overall.

Temporary tables exist in Oracle, SQL Server, PostgreSQL, and MySQL (and possibly other database vendors as well). In this article, I’ll explain how they work and how to create them in each of these databases.

 

SQL Server Temp Table

SQL Server allows for two types of temporary tables:

• Local Temporary Tables are visible only in the current session
• Global Temporary Tables are visible to all sessions

 

How to Create a Temporary Table in SQL Server

Creating a temporary table in SQL Server is similar to creating a normal table. There are two ways to create this table:

• Using CREATE
• Using SELECT INTO

Here’s an example of using the CREATE statement:

CREATE TABLE #temp_customers (
id INT,
cust_name VARCHAR(100)
);

A # character must be provided as a prefix to the table name to mark it as a temporary table. There is no “TEMPORARY” keyword that can be used in SQL Server.

This is also a “local temporary table” because it starts with a single #.

A “global temporary table” starts with two hash characters: ##.

The SQL temp table is now created. To store data in it, you can run an INSERT statement just like with a normal table:

INSERT INTO #temp_customers (id, cust_name)
SELECT id, cust_name
FROM customer
WHERE cust_type = 'R';

The #temp_customers table will now contain the data from the SELECT query. It can be accessed within the session by the current connection.

Notice that the INSERT statement refers to the table with a # as a prefix. If the # prefix is omitted, then the table will not be found and you’ll get an error.

You can also create a temporary table in SQL Server by using the SELECT INTO syntax:

SELECT id, cust_name
INTO #temp_customers
FROM customer
WHERE cust_type = 'R';

This will create a temporary table called #temp_customers and insert the results of the SELECT query into it in a single statement. You don’t need to specify the data types as they will be determined from the source table, so this may be an easier way for you to populate the table.

All SQL temp tables are created in the dbo schema. They are dropped when the session ends.

 

Global Temporary Tables in SQL Server

Global temporary tables are accessible to all connections. They are created in a similar way to local temporary tables, except you use a double # instead of a single # as a prefix.

They can be created in the same way as a local temporary table, using CREATE or SELECT INTO.

Here’s an example using CREATE:

CREATE TABLE ##temp_customers (
id INT,
cust_name VARCHAR(100)
);

Here’s an example using SELECT INTO:

SELECT id, cust_name
INTO ##temp_customers
FROM customer
WHERE cust_type = 'R';

Notice the syntax is the same, but they have a double # character as a prefix.

 

Drop a Temporary Table

In SQL Server, a temporary table is deleted or dropped when the connection that created it is closed. This is when you disconnect from the database, or when you close the query window in SSMS (or another IDE) and don’t save the changes.

If you want to drop the temporary table manually, you can do so in the same way as dropping a normal table:

DROP TABLE #temp_customers;

This will drop the temporary table from the database.

The same method can be used on global tables:

DROP TABLE ##temp_customers;

 

Oracle Temporary Table

Oracle supports two types of temporary tables.

• Global Temporary Tables
• Private Temporary Tables

Global temporary tables are objects that are just like regular tables, but their data is private and removed at the end of the session. Other sessions can use the global temporary table, but they will have their own set of data.

Private temporary tables work in a similar way to local temporary tables in SQL Server. They are removed at the end of the session.

Private temporary tables are a new feature in Oracle 18c, so if you use an older version you won’t be able to use this feature.

 

Create a Global Temporary Table in Oracle

To create a global temporary table, you run a CREATE statement:

CREATE GLOBAL TEMPORARY TABLE temp_customers (
id NUMBER(10),
cust_name VARCHAR2(100)
)
ON COMMIT DELETE ROWS;

The “GLOBAL TEMPORARY” keywords as part of the CREATE statement indicate it is a global temporary table. The columns and name are specified in the same way as a normal table.

Also, there is a clause available for the end of the statement, which can be either one of:

• ON COMMIT DELETE ROWS
• ON COMMIT PRESERVE ROWS

If you use ON COMMIT DELETE ROWS, then when a commit is performed, the rows are deleted from the table. This means the rows are kept for the length of the transaction (you can learn more about transactions here).

If you instead use ON COMMIT PRESERVE ROWS, then the rows are kept for the length of the session. They are deleted at the end of the session.

To insert data into a global temporary table, you can use the same INSERT statement as you would use for a regular table.

INSERT INTO temp_customers (id, cust_name)
SELECT id_cust_name
FROM customer
WHERE cust_type = 'R';

The results of the SELECT query are then inserted into the temp_customers table.

 

Create a Private Temporary Table in Oracle

A private temporary table is like a local temporary table in other databases. It’s a table object that only exists for the current session or the current transaction.

As mentioned earlier, private temporary tables are a new feature in Oracle 18c. If you’re using an older version, you won’t be able to use private temporary tables.

To create a private temporary table in Oracle, you need to do two things:

• Use PRIVATE TEMPORARY in the CREATE statement
• Ensure your table name begins with “ora$ptt_”

I don’t know why you need to use both of these. I would have thought using PRIVATE would be enough, but if you don’t use the table name prefix then you’ll get an error.

Here’s an example of creating a private temporary table in Oracle:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_customers (
id NUMBER(10),
cust_name VARCHAR2(100)
)
ON COMMIT DROP DEFINITION;

Just like the global temporary table, the private temporary table has an ON COMMIT clause. You have two options:

• ON COMMIT DROP DEFINITION: the table is dropped at the end of the transaction (on commit).
• ON COMMIT PRESERVE DEFINITION: the table definition and data is kept after the transaction but is dropped at the end of the session.

Temporary tables can also be created using the Create Table as Select method:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_customers AS
SELECT id, cust_name
FROM customer
WHERE cust_type = 'R';

 

Drop a Temporary Table

You can drop a temporary table in Oracle in the same way as dropping a regular table:

DROP TABLE temp_customers;

The SQL temp table is then dropped from the database.

 

Notes about Temporary Tables

• If you run a TRUNCATE statement on a temporary table, only the data from the current session is removed. Data used by other sessions is not removed.
• Indexes can be created on temporary tables.
• Temporary tables can have triggers created on them.
• Views can be created on temporary tables.

 

MySQL Temporary Table

Temporary tables can be created in MySQL. They work in a similar way to SQL Server’s local temporary tables and Oracle’s private temporary tables.

 

Create a Temporary Table in MySQL

To create a temporary table in MySQL, you simply add the word TEMPORARY to the CREATE TABLE statement:

CREATE TEMPORARY TABLE temp_customers (
id NUMBER(10),
cust_name VARCHAR2(100)
);

You can also use the Create Table as Select syntax:

CREATE TEMPORARY TABLE temp_customers AS
SELECT id, cust_name
FROM customer
WHERE cust_type = 'R';

The data in a SQL temp table is only visible within the current session. Other sessions cannot access the temporary table you have created.

 

Drop a Temporary Table in MySQL

Temporary tables are automatically dropped at the end of a session.

If you want to manually drop a temporary table, you can do so with the DROP TABLE command:

DROP TABLE temp_customers;

The table will then be dropped.

You can use the TEMPORARY word here as well, to make it clear that you’re dropping a temporary table.

DROP TEMPORARY TABLE temp_customers;

It’s good practice to use the TEMPORARY keyword to make it clear. If you try to use the TEMPORARY keyword when dropping a non-temporary table, you’ll get an error.

 

PostgreSQL Temporary Table

Temporary tables in PostgreSQL work in a similar way to other databases. You can create a temporary table that exists until the end of your database session.

 

Create a Temporary Table in PostgreSQL

To create a temporary table in PostgreSQL you use the CREATE TEMPORARY TABLE statement:

CREATE TEMPORARY TABLE temp_customers
SELECT id, cust_name FROM customer;

You can also use the TEMP keyword instead:

CREATE TEMP TABLE temp_customers
SELECT id, cust_name FROM customer;

These examples create a table based on an existing table, but you can also create a new table from scratch:

CREATE TEMPORARY TABLE temp_customers (
id NUMBER(8),
cust_name VARCHAR(100)
);

There are two keywords called GLOBAL and LOCAL that can be specified when creating a temporary table. However, they both do the same thing and are deprecated features, so I would avoid them.

When you create a table, you can also specify an optional parameter called ON COMMIT. This will determine what happens when you commit your transaction.

CREATE TEMPORARY TABLE temp_customers
SELECT id, cust_name FROM customer
ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }

The three options are:

• Preserve Rows: The rows are kept at the end of the transaction. The default behaviour.
• Delete Rows: All rows in the table are deleted at the end of the transaction.
• Drop: The temporary table is dropped at the end of the transaction.

For example, to delete rows at the end of the transaction:

CREATE TEMPORARY TABLE temp_customers
SELECT id, cust_name FROM customer
ON COMMIT DELETE ROWS;

 

Drop a Temporary Table in PostgreSQL

SQL temp tables in PostgreSQL are automatically dropped at the end of the session. If you want to drop them at the end of the transaction, you can use the ON COMMIT DROP parameter when creating the table:

CREATE TEMPORARY TABLE temp_customers
SELECT id, cust_name FROM customer
ON COMMIT DROP;

If you want to drop the table at any point you like, you can use the DROP TABLE statement just like a regular table:

DROP TABLE temp_customers;

The temporary table will then be dropped.

 

Conclusion

Temporary tables can be helpful when working with data sets more than once in a session and can be a good way to speed up your overall SQL, whether it’s a stored procedure or a script. They work in a similar way in all the major database vendors but the syntax is slightly different.