MySQL - How to create a temporary table from the results of SELECT statement?

You want to create a table after a select statement as you don’t want to specify the column and it’s for temporary uses only.

Syntax to create a temporary table:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] temp_table
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] (query_expression);

For example, to create a temporary table temp_cs which contains only two columns customerNumber and customerName from table customers:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_cs
AS (
    SELECT
        customerNumber,
        customerName
    FROM
        customers);

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.