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 12.2.5.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 12.7.3.3, “SELECT ... INTO
Statement”.
User Comments
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.
With INSERT..SELECT you must first have created the table. If you wish to create the table with the select, use CREATE..SELECT.
If table structure is the same then you can use:
INSERT INTO `table2` SELECT * FROM `table1`;
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!
There is an alternative for the "select into problem":
create table mynewtable (select * from myoldtable)
Add your own comment.