Create a temporary table in a SELECT statement without a separate CREATE TABLE
Solution 1
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)
From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.
Solution 2
In addition to psparrow's answer if you need to add an index to your temporary table do:
CREATE TEMPORARY TABLE IF NOT EXISTS
temp_table ( INDEX(col_2) )
ENGINE=MyISAM
AS (
SELECT col_1, coll_2, coll_3
FROM mytable
)
It also works with PRIMARY KEY
Solution 3
Use this syntax:
CREATE TEMPORARY TABLE t1 (select * from t2);
Solution 4
Engine must be before select:
CREATE TEMPORARY TABLE temp1 ENGINE=MEMORY
as (select * from table1)
Solution 5
ENGINE=MEMORY
is not supported when table contains BLOB
/TEXT
columns
700 Software
Join 700.social ! A happy medium between Facebook and Gab. :) The name is too long but the domain looks good. Also, Software Development / Consulting (423) 802-8971 700software.com old username: George Bailey (but now I use my real name) http://www.google.com/images?q=George+Bailey
Updated on July 08, 2022Comments
-
700 Software almost 2 years
Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I want to re-use.
It would save time if I did not have to write up a create table command and keep the column list and type list matched up.