SELECT INTO in MySQL

Question

I am a MSSQL user and now I am converting my database to MySQL. I am writing the following query in MySQL:

SELECT * INTO new_tbl FROM tbl;

And I get the following error

Error : Undeclared variable new_tbl

How such a query should be properly written in MySQL?

score 153 · Accepted Answer · answered May 29 '13 at 09:01

153

Use the CREATE TABLE SELECT syntax.

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

answered May 29 '13 at 09:01

Dave K

SELECT INTO command creates new table and can be copied data from one database to another as – Shankar Regmi Nov 08 '14 at 17:16
3

Correct syntax is: CREATE TABLE new_tbl AS SELECT * FROM orig_tbl; – csname1910 Jan 21 '16 at 22:59
CREATE ... SELECT works, this should be the accepted answer. The "AS" keyword is apparently optional, and SELECT INTO doesn't work with MySQL. – leo Aug 01 '18 at 04:10
Emphasis that it does not create indexes! – Glorious Kale Jan 03 '20 at 09:35

score 101 · Answer 2 · answered May 29 '13 at 08:52

101

In MySQL, It should be like this

INSERT INTO this_table_archive (col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM this_table
WHERE entry_date < '2011-01-01 00:00:00';

answered May 29 '13 at 08:52

Muhammad Hani

10

Subtle difference. In this case the table has to exist. For `select into`, it doesn't. In fact, I think it's an error if it does. So this solution should be prefaced with statements that duplicate the table schema. – paxdiablo May 29 '13 at 08:55
As paxdiablo says, this requires the table you are inserting into to already exist. The MSSQL syntax that OP is used to creates the table for you. – Dave K May 29 '13 at 09:04
8

May not have answered the OP's question but fortunately this was exactly what I was looking for. – garyh Jan 28 '14 at 14:47
THIS CAN BE A BAD THING!!!! To me this is not a good answer because the standard Insert / Select locks the tables you are selecting and joining on. If you have several thousand records this will freeze all those tables. The select into tables does not lock the tables being selected and joined on. – wondernate Jul 22 '21 at 16:54

2 Answers2