6.4.3.1 INSERT ... SELECT Syntax

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

With INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

The following conditions hold for an INSERT ... SELECT statement:

• The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subquery clauses, the situation could easily be very confusing.)
• AUTO_INCREMENT columns work as usual.
• In C programs, you can use the C API function mysql_info() to get information about the query. See section 6.4.3 INSERT Syntax.
• To ensure that the binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during INSERT ... SELECT.

You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded.

User Comments

The fields have to be in the same order. If the
existing table has fields B, C, A, and the table being
append has fields A, B, C, the data in A goes into the
B field, B goes into the C field, and C goes into the A
field.

As a workaround for sub select or views, I have
found 'create table' useful:
drop table temp; create temporary table temp
select * from testtable;

OR use the 'IN' syntax ref.: 1.7.4.1 SubSELECTs

It seems INSERT_SELECT has issues with field type
DATE.

Attempting to use the INSERT_SELECT construct
yields corrupted dates. For example, 2002-12-09
becomes 2012-09-02.

Select from two table will not work.

The insert_select will not work, if selecting specific values with specific conditions using select.

insert into A select '0', Ab.date, 1 from A as Ab where Ab.log = 'Z';
will not work under mysql !

Your auto_increment field will be an issue.

The trick is, if you want your auto_increment to work, you have to leave it out in the grammar:

ccl.ccl_maintest.id is an int(10), auto_increment, PRIMARY and Next_AutoIndex = 52

<b>This worked (I don't include "id"):</b>

INSERT INTO ccl.ccl_maintest<b>( YR, AU, ST, SD, SC )</b> SELECT YR, AU, ST, SD, SC FROM jdaxell.ccl WHERE id = 24

It inserted jdaxell.ccl.id=24 correctly into ccl.ccl_maintest where ccl.ccl_maintest.id = 53.

This did not work (I include "id"):

INSERT INTO ccl.ccl_maintest( id, YR, AU, ST, SD, SC ) SELECT id, YR, AU, ST, SD, SC FROM jdaxell.ccl WHERE id = 24

In my second example, it copied from jdaxell.ccl.id=24 and inserts at ccl.ccl_maintest.id=24. Auto_Increment on <b>the auto_incremented field "id" will not work if you declare it in the insert & select statements</b>.

Likewise this code is useless, "if" you want auto_increment to work.

insert into ccl.ccl_maintest (*) select * FROM jdaxell.ccl WHERE id = 24

I do not understand why "it's forbidden in standard SQL to SELECT from the same table into which you are inserting".

It is not correct statement - there is no discrepancies: SELECT just must complete before INSERT begin.

For example, INSERT INTO tab VALUES (1, (SELECT MAX(N) FROM tab)) works correctly in Oracle databse.

I know that its not ANSI correct to use INSERT INTO tab VALUES (1, (SELECT MAX(N) FROM tab)), but mysql should be able to do it when is not in ansi strict mode, because when i need to have a serialized number for invoices or what not, instead of having to lock tables, get the next number and do the insert, i would instead just go INSERT INTO invoices VALUES ("these are some rows with invoice consecuent number", (SELECT MAX(invoicenumber)+1 FROM invoices))
OR
Does anyone know a way to do what i just did above in just one query and not breaking the ANSI? (it has to be without making it an auto_increment column)

There is a change in the way INSERT INTO ... SELECT functionality works between 3.23.49a and 4.0.13, which does not appear in the documentation anywhere, and caused a problem in my application.

In 3.23.49a, if you do two INSERT INTO statements into a table with a primary key, and there are duplicate primary keys in the second INSERT statement, that statement will execute correctly, automatically ignoring the duplicates.

In 4.0.13, this does not happen - you need to specifically IGNORE the duplicate entries, using INSERT INTO IGNORE.

I couldn't find any reference to this in the documentation, but hopefully this will help someone out.

If someone needs to Insert more than a value from a database (like db-value + php-variables):

mysql_query("INSERT INTO table1 (column1, column2, column3, ...) SELECT '$PHP-value1', table2.db_value, '$PHP-value2', ... FROM table2 WHERE table2.column='$PHP-value'");

works fine :-)

Actually, since MySQL 4.0.14, you can do INSERT ... SELECT with the same table name in the INSERT and SELECT statement:

INSERT INTO table ( field ) SELECT field FROM table WHERE field = "data";

Add your own comment.