SQL - Temporary Tables

---

What are Temporary Tables?

Temporary tables are pretty much what their name describes: they are tables that are created in a database to store data temporarily. They can perform operations that are similar to operations of permanent database tables like Create, Update, Delete, Insert and also other operations like Join. But these tables will be automatically deleted once the current client session is terminated. In addition to that, they can also be explicitly deleted if the users decide to drop them manually.

Various RDBMS, like MySQL, support temporary tables. Temporary tables are available from MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23, you can't use temporary tables, but you can use heap tables.

As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Types of Temporary Tables

There are two types of temporary tables which are categorized based on their scope of working −

• Local Temporary Tables

• Global Temporary Tables

Local Temporary Tables

A Local Temporary Table is accessible only in the session that has created it. It is automatically deleted when the connection that has created it gets closed. To create Local Temporary Table, a single “#” is used as the prefix of a table name. To manually drop this temporary table by using the “DROP TABLE #temp-table” query. There will be Random Numbers are appended to the Name of Table Name. If the Temporary Table is created inside the stored procedure, it get dropped automatically upon the completion of stored procedure execution.

Global Temporary Tables

Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name. There will be no random Numbers suffixed at the end of the Table Name.

To create a Global Temporary Table, add the “##” symbol before the table name.

Creating Temporary Tables

To create temporary tables in SQL, we follow the same query as creating regular database tables. However, instead of using the CREATE TABLE statement, you use the CREATE TEMPORARY TABLE statement. Following is the example to create a sample temporary table −

Example

Here is an example showing you the usage of a temporary table.

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
   -> product_name VARCHAR(50) NOT NULL
   -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY
   -> (product_name, total_sales, avg_unit_price, total_units_sold)
   -> VALUES
   -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;

+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

When you issue a SHOW TABLES command, then your temporary table will not be listed out in the list. Now, if you log out of the MySQL session and then issue a SELECT command, you will find no data available in the database. Even your temporary table will not be existing.

Dropping Temporary Tables

By default, all the temporary tables are deleted by MySQL when your database connection gets terminated. Still if you want to delete them in between, then you can do so by issuing a DROP TABLE command.

Example

Following is an example on dropping a temporary table.

mysql> CREATE TEMPORARY TABLE SALESSUMMARY (
   -> product_name VARCHAR(50) NOT NULL
   -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SALESSUMMARY
   -> (product_name, total_sales, avg_unit_price, total_units_sold)
   -> VALUES
   -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SALESSUMMARY;

+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE SALESSUMMARY;
mysql> SELECT * FROM SALESSUMMARY;

ERROR 1146: Table 'TUTORIALS.SALESSUMMARY' doesn't exist