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)

George John

Published on 29-Nov-2018 07:19:22

Previous Page Print Page