MySQL :: MySQL 8.0 Reference Manual :: 1.8.2.1 SELECT INTO TABLE Differences

MySQL 8.0 Reference Manual / ... / SELECT INTO TABLE Differences

Pre-General Availability Draft: 2017-07-17

1.8.2.1 SELECT INTO TABLE Differences

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. See Section 13.2.6.1, “INSERT ... SELECT Syntax”. For example:

INSERT INTO tbl_temp2 (fld_id)
    SELECT tbl_temp1.fld_order_id
    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Alternatively, you can use SELECT ... INTO OUTFILE or CREATE TABLE ... SELECT.

You can use SELECT ... INTO with user-defined variables. The same syntax can also be used inside stored routines using cursors and local variables. See Section 13.2.10.1, “SELECT ... INTO Syntax”.

PREV HOME UP NEXT

User Comments

Posted by Sean Nolan on March 24, 2005

This topic states that INSERT ... SELECT is basically the same as the Sybase SELECT ... INTO TABLE statement. That is wrong, the two are very different. SELECT ... INTO TABLE creates a NEW table with the rows from the SELECT, this is completely different from INSERT ... SELECT, which just appends rows to an existing table.

It is correct to say that the MySql CREATE TABLE ... SELECT statement is basically the same as the Sybase SELECT ... INTO TABLE statement.

Microsoft SQL Server also supports SELECT ... INTO TABLE.

Posted by Ann B on July 29, 2005

With INSERT..SELECT you must first have created the table. If you wish to create the table with the select, use CREATE..SELECT.

Posted by Geo Cabs on April 26, 2006

If table structure is the same then you can use:
INSERT INTO `table2` SELECT * FROM `table1`;

Posted by Matthew Green on May 29, 2006

Syabse select/into is also a minimally logged operation, making it much faster than insert....select. So, as already stated it is not the same thing by a long way!

Posted by Heinz Mueller on November 4, 2009

There is an alternative for the "select into problem":

create table mynewtable (select * from myoldtable)

Posted by M. Hamed A. on December 3, 2011

SELECT INTO tries to make a table based on the output of the SELECT statement but it lacks many definitions those you could have in CREATE TABLE statement. for instance the indexes (PRIMARY, UNIQUE, etc.) are not created in output table of SELECT INTO command or you can't set a DEFAULT for some created fields.

According to this situation, CREATE ... SELECT command of MySQL is somehow better and more complete than the SELECT INTO syntax.