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 pke Create, Update, Delete, Insert and also other operations pke Join. But these tables will be automatically deleted once the current cpent session is terminated. In addition to that, they can also be exppcitly deleted if the users decide to drop them manually.

Various RDBMS, pke 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 earper, temporary tables will only last as long as the session is apve. 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 cpent program, then the temporary table will exist until you close the cpent 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 psted out in the pst. 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