SQL Temporary Tables

Temporary Tables are tables that are visible only to the current session and are removed automatically when the session is closed.

Temporary Tables permit to store and process intermediate results by using the same SELECT, INSERT, UPDATE, JOIN, and DELETE used with the classical SQL tables.

There are some database systems that support Temporary Table, like MySQL, SQLServer, Oracle.

Syntax

In MySQL, you can use the CREATE TEMPORARY TABLE statement to create a temporary table:

CREATE TEMPORARY TABLE table_name (columns definitions);

Demo Table

Let us suppose that we have a table called "Employee" with the following data:

Creating a Temporary Table by Copying an Existing Table

The Temporary Table can be used when you want to test or do some experiences with SQL queries without affecting the database.

The following SQL statement creates a Temporary Table named "Employee_temp" populated with data from the "Employee" table.

mysql> CREATE TEMPORARY TABLE employee_temp SELECT * FROM employee;

After executing the above statement, if we select data from the new created Temporary Table using the following command:

SELECT * FROM employee_temp;

We will get the following output :

+--------+------------------+------------+------------+-----------+------------------+
| emp_no | name             | birth_date | hire_date  | salary    | dept_name        |
+--------+------------------+------------+------------+-----------+------------------+
| 1001   | James Smith      | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria Martinez   | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna Williams   | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James Johnson    | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie Miller | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1008   | Nelson Lopez     | 1999-05-26 | 2010-10-06 | 7300      | Marketing        |
+--------+------------------+------------+------------+-----------+------------------+

Note: A Temporary Table can have the same name as a permanent table. When you specify the Temporary Table's same name as for the permanent table, then the permanent table is hidden until the temporary table is deleted.

Dropping Temporary Tables

The Temporary Tables are automatically deleted when the database session or connexion they are created is closed.

In the case you want to delete Temporary Tables without closing the current session, you can use the DROP TEMPORARY TABLE statement.

The following SQL statement deletes the Temporary Table named "Employee_temp" from the database.

mysql> DROP TEMPORARY TABLE employee_temp;