How to create a temporary MySQL table in a SELECT statement without a separate CREATE TABLE?

---

To create a temporary table in a SELECT statement we use TEMPORARY keyword.

This temporary table will be visible for the current session and whenever a session is closed, it is automatically destroyed. Two sessions can use the same temporary table.

Creating a table.

mysql> create table MyTableDemo
   -> (
   -> id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.69 sec)

Inserting some records.

mysql> insert into MyTableDemo values(1,'John');
Query OK, 1 row affected (0.18 sec)

mysql>  insert into MyTableDemo values(2,'Carol');
Query OK, 1 row affected (0.13 sec)

mysql>  insert into MyTableDemo values(3,'Bob');
Query OK, 1 row affected (0.12 sec)

To display all records.

mysql> select *from MyTemporaryTableDemo;

Here is the output.

+------+-------+
| id   | Name  |
+------+-------+
|    1 | John  |
|    2 | Carol |
|    3 | Bob   |
+------+-------+
3 rows in set (0.00 sec)

The syntax to create a temporary table.

 CREATE TEMPORARY TABLE IF NOT EXISTS yourTemporaryTableName AS (SELECT * FROM yourTableName);

Let us now implement the above syntax in the following query −

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS MyTemporaryTableDemo AS (SELECT * FROM MyTableDemo);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

To check if all records are copied successfully.

mysql> select *from MyTemporaryTableDemo;

Here is the output.

+------+-------+
| id   | Name  |
+------+-------+
|    1 | John  |
|    2 | Carol |
|    3 | Bob   |
+------+-------+
3 rows in set (0.00 sec)

• Related Questions & Answers
• Create a temporary table in a MySQL procedure?
• Create a temporary table similar to a regular table with MySQL LIKE
• Create a temporary table with dates in MySQL
• How can we create a MySQL temporary table by using PHP script?
• Create MySQL query to create a table from an existing table?
• How to create a MySQL table with InnoDB engine table?
• How to create a MySQL table with MyISAM engine table?
• Create a table in MySQL that matches another table?
• How to create a MySQL table with indexes?
• MySQL Stored Procedure to create a table?
• INSERT INTO table if a table exists in MySQL else implement CREATE TABLE and create the table
• How to create a table from view in MySQL?
• How to create a table in PostgreSQL?
• How to create a table in JDBC using another table?
• How to create a table with a caption?