Create Temporary Table from SELECT Query in MySQL

Sometimes you may need to create a table from a select SQL statement without using CREATE TABLE statement in MySQL. When you use CREATE TABLE it creates a persistent table in your database which continues to occupy space all the time. If you use derived tables for this purpose they will last only till the statement is executed and get deleted immediately afterwards. Sometimes you may want to create a table only for the duration of a session, which automatically gets deleted afterwards. They are called as temporary tables in MySQL. In this article, we will learn how to create temporary table from SELECT query.

Create Temporary Table from SELECT Query in MySQL

Temporary tables are available only during the session in which they are created and get deleted automatically when the session ends. It means that two different sessions can have temporary tables with same table name. You can also have a temporary table with the same name as another existing non-temporary table. In such cases, the existing table is hidden until the temporary table gets dropped.

Here is the syntax to create temporary table using CREATE TEMPORARY TABLE statement.

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)

In the above query, we specify table name table2 as the name of temporary table. We also specify the data to be used to populate this table in (…). Once a temporary table is created, you can use it till the end of your current session.

If you need to add index to temporary table you can do so as shown below.

CREATE TEMPORARY TABLE IF NOT EXISTS 
  temp_table ( INDEX(col_2) ) 
ENGINE=MyISAM 
AS (
  SELECT col_1, coll_2, coll_3
  FROM mytable
)

In this article, we have learnt how create temporary table in MySQL with SELECT query, without using CREATE TABLE statement. This is useful in case you want a table that is temporary but more durable than a derived table but less durable than a permanent table.

Also read:

How to Select Multiple Columns in Python Pandas
How to Sort Python List By Multiple Elements
How to Remove Duplicates in Python Pandas
How to Check if String is Integer in Python
How to Shuffle List of Objects in Python