567

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.

einpoklum
  • 118,144
  • 57
  • 340
  • 684
700 Software
  • 85,281
  • 83
  • 234
  • 341

6 Answers6

895
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.

maxhb
  • 8,554
  • 9
  • 29
  • 53
psparrow
  • 9,808
  • 1
  • 17
  • 11
  • 8
    Perfect! Columns with optimum maxlengths and all! I added the word `temporary` so `create temporary table mytable as select ...`. – 700 Software May 02 '11 at 16:36
  • How would you specify the engine of that table? e.g. MEMORY I have tried ENGINE=MEMORY but it gives me a syntax error. – imperium2335 Jun 26 '12 at 06:57
  • 5
    @imperium2335, Perhaps you should try the following: `create table t as select ... limit 0; alter table t engine=memory; insert into t select ...`. Or, perhaps you can change the "default engine of new tables". I imagine this can be done in a session level variable. Better yet, use the Ask Question button on the upper-right. – 700 Software Jun 29 '12 at 16:05
  • How does this answer the question when Create Table is clearly used. -1 – John Riselvato Feb 08 '13 at 19:07
  • 11
    It doesn't require knowing about the column names and types, which was the questioner's reason for wanting to avoid using Create Table. – psparrow Feb 08 '13 at 23:13
  • @John, I concur. This answers my question perfectly. – 700 Software Feb 14 '13 at 02:30
  • if this is only a temporary table, will it get deleted? – hearmeroar Apr 07 '14 at 20:20
  • @GeorgeBailey I don't really know how the engine specification works. Is it dangerous not to specify? – rschwieb May 29 '14 at 14:45
  • @rschwieb, I believe it's perfectly safe. For me it defaults to MyISAM, which is certainly not going to pose any problems. If you are wondering what the default was for you, you can run `create temporary table x` and then show `create table x` and it will show the engine it used. In the future you should use **Ask Question** instead of **add comment** because that way you usually get better answers. – 700 Software May 29 '14 at 22:45
  • 42
    you can use it like this `CREATE TEMPORARY TABLE IF NOT EXISTS table2 LIKE table1` if you dont want to copy data, just structure – dzona Jul 22 '14 at 20:47
  • 3
    what do you mean by session ? – Saurabh Chandra Patel Nov 23 '16 at 17:52
  • 1
    @SaurabhChandraPatel under sessions means the time period while current database connection is alive. When you close the connection - the session also closes. – Andrey Rudenko Feb 20 '18 at 16:28
  • Man, this syntax is so concise, readable and powerful. I love it. – Zimano Nov 22 '19 at 12:55
156

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

Serge S.
  • 4,855
  • 3
  • 42
  • 46
RafaSashi
  • 16,483
  • 8
  • 84
  • 94
  • 3
    Can Engine=Memory be used too with such syntax? – DarkSide Jun 25 '13 at 21:59
  • 6
    @DarkSide Yes ENGINE=MEMORY can also be used. See the following example: http://blog.cnizz.com/2010/11/24/mysql-temporary-tables-example-optimizing-applications-with-temp-tables/ – RafaSashi Jul 26 '13 at 10:30
  • 1
    what is the difference between MyISAM and Memory engine? what are the benefits of memory? – yeahman Nov 21 '18 at 19:46
  • 1
    @yeahman MyISAM engine stores data in the disk but the memory engine keeps it in RAM. P.S: the memory engine doesn't support `transactions`, table level locking only... read more: https://dba.stackexchange.com/questions/1811/what-are-reasons-not-to-use-the-memory-storage-engine-in-mysql/1812 – parse Jan 12 '21 at 21:33
68

Use this syntax:

CREATE TEMPORARY TABLE t1 (select * from t2);
shA.t
  • 16,580
  • 5
  • 54
  • 111
rizon
  • 8,127
  • 1
  • 27
  • 17
58

Engine must be before select:

CREATE TEMPORARY TABLE temp1 ENGINE=MEMORY 
as (select * from table1)
Crusader
  • 1,182
  • 11
  • 22
44

ENGINE=MEMORY is not supported when table contains BLOB/TEXT columns

shA.t
  • 16,580
  • 5
  • 54
  • 111
Cris
  • 2,824
  • 24
  • 23
1

As I understand it, a SELECT statement will work on the temporary table if you're using it in something like phpMyAdmin, but following that SELECT, the temporary table will be gone. This means set up exactly what you want to do with it first, and don't view any results till your 'action' statements that change the data (DELETE, UPDATE) are complete.

Pete855217
  • 1,570
  • 5
  • 23
  • 35