SQL Temporary Table

In this tutorial, you will learn how to create a temporary table using SQL.

Create Temporary Table

Temporary tables are visible only to the current session and are automatically deleted when the session that created the table is closed.

Therefore, since temporary tables are not permanently stored in the database, they are very useful when you only need to execute or test certain tables for a short time and then want the table to disappear automatically.

Syntax

The CREATE TEMPORARY TABLE statement is used to create a temporary table.

CREATE TEMPORARY TABLE table_name (column definitions);

If you want to create a temporary table from scratch, you can use the keyword TEMPORARY when creating the table, that is, use CREATE TEMPORARY TABLE instead of the CREATE TABLE statement. For complete syntax and examples, see Create TableChapter.

Create a temporary copy of the existing table

A temporary table can be very useful when you only want to test SQL queries without affecting the database. Let'sIn the MySQL databaseCreate a temporary copy of the existing table.

Type the following command at the MySQL command prompt and press Enter:

mysql> CREATE TEMPORARY TABLE persons SELECT　*　FROM persons;

The statement dynamically creates a temporary table named 'persons' from the result set of the existing base table 'persons'. Additionally, since it is a temporary copy of the 'persons' table, you can executeINSERT,UPDATEorDELETEand any other operations, without worrying about mistakenly affecting the original persons base table.

Delete Temporary Table

Temporary tables are automatically deleted after the database connection or session in which they were created is closed. However, if you want to delete them without closing the current session, you can use the following DROP TEMPORARY TABLE statement:

mysql> DROP TEMPORARY TABLE persons;

The above statement will be frompersonsDelete the temporary table from the database. After that, the originalpersonsThe base table will become visible.