Introduction of SQL SELECT ... INTO - Why will it not run?

Question

I'm running a SQL server and need to run some commands using the SELECT ... INTO commands. At the moment (as a test) I am running this command:

SELECT * 
INTO `assets_copy` 
FROM `assets`

The simplest example possible, and yet it still won't run. I get the error:

MySQL said:  

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'assets_copy` FROM `assets`  LIMIT 0, 30' at line 1 

So I'm wondering if the version of SQL I'm using doesn't support this? I'm using 3.23.49 of MySQL.

Answer 1

Based on the documentation, MySQL does not support the SELECT INTO syntax. Instead you have to use

INSERT INTO assets_copy
  SELECT * FROM assets;

MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing.

Answer 2

You can use

CREATE TABLE dest_db.table_name AS SELECT * FROM src_db.table_name;

For more details you can checkout this link