MySQL Create and Drop Temporary Table

MySQL support to create a temporary table with the help of "TEMPORARY" keyword. MySQL temporary table is a special type of table that hold temporary result set, which you can reuse several times in a single session and is dropped automatically when the session is closed.

The creating session can perform any operation on the temporary table, such as DROP TABLE, INSERT, UPDATE, or SELECT.

MySQL Create Temporary table

MySQL CREATE TEMPORARY TABLE Syntax

To create a temporary table, use the following syntax:

CREATE TEMPORARY TABLE tablename
SELECT * FROM existingtable;

Parameters:

tablename: The temporary table name that you want to create.

TEMPORARY: The temporary flag allows you to create temporary tables only.

existingtable: The existing table to produce result set.

MySQL CREATE TEMPORARY TABLE example

CREATE TEMPORARY TABLE tblTempCustomer
SELECT * FROM tblCustomerLoan;

Note:

In the above example, we created temporary table "tblTempCustomer".

You can use MySQL Command Line Client to create temporary table. It will look like this:

Check CREATED TEMPORARY TABLE

SELECT * FROM tblTempCustomer; 

You can use MySQL Command Line Client to show renamed table name. It will look like this:

MySQL DROP TEMPORARY TABLE Syntax

To remove a temporary table, use the following syntax:

DROP TEMPORARY TABLE temptablename;

Parameters:

temptablename: The temporary table name that you want to remove.

MySQL DROP TEMPORARY TABLE example

DROP TEMPORARY TABLE tblTempCustomer;

Note:

In the above example, we removed a temporary table called "tblTempCustomer".

You can use MySQL Command Line Client to remove temporary table. It will look like this:

Check DROP TEMPORARY TABLE

SELECT * FROM tblTempCustomer; 

You can use MySQL Command Line Client to check existance of removed temporary table. It will look like this: