Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 45.6Mb
PDF (A4) - 45.7Mb
PDF (RPM) - 41.2Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 209.9Kb
Man Pages (Zip) - 312.1Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  INSERT ... SELECT Syntax INSERT ... SELECT Syntax

    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

    {expr | DEFAULT}

    col_name = value

    assignment [, assignment] ...

With INSERT ... SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables. For example:

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

The following conditions hold for INSERT ... SELECT statements:

  • Specify IGNORE to ignore rows that would cause duplicate-key violations.

  • The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. However, you cannot insert into a table and select from the same table in a subquery.

    When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, you cannot use INSERT INTO t ... SELECT ... FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement. See Section 8.4.4, “Internal Temporary Table Use in MySQL”, and Section B.6.6.2, “TEMPORARY Table Problems”.

  • AUTO_INCREMENT columns work as usual.

  • To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT ... SELECT statements (see Section 8.11.3, “Concurrent Inserts”).

  • To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

You can explicitly select which partitions or subpartitions (or both) of the source or target table (or both) are to be used with a PARTITION option following the name of the table. When PARTITION is used with the name of the source table in the SELECT portion of the statement, rows are selected only from the partitions or subpartitions named in its partition list. When PARTITION is used with the name of the target table for the INSERT portion of the statement, it must be possible to insert all rows selected into the partitions or subpartitions named in the partition list following the option. Otherwise, the INSERT ... SELECT statement fails. For more information and examples, see Section 23.5, “Partition Selection”.

For INSERT ... SELECT statements, see Section, “INSERT ... ON DUPLICATE KEY UPDATE Syntax” for conditions under which the SELECT columns can be referred to in an ON DUPLICATE KEY UPDATE clause.

The order in which a SELECT statement with no ORDER BY clause returns rows is nondeterministic. This means that, when using replication, there is no guarantee that such a SELECT returns rows in the same order on the master and the slave, which can lead to inconsistencies between them. To prevent this from occurring, always write INSERT ... SELECT statements that are to be replicated using an ORDER BY clause that produces the same row order on the master and the slave. See also Section, “Replication and LIMIT”.

Due to this issue, INSERT ... SELECT ON DUPLICATE KEY UPDATE and INSERT IGNORE ... SELECT statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. (Bug #11758262, Bug #50439)

See also Section, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

An INSERT ... SELECT statement affecting partitioned tables using a storage engine such as MyISAM that employs table-level locks locks all partitions of the target table; however, only those partitions that are actually read from the source table are locked. (This does not occur with tables using storage engines such as InnoDB that employ row-level locking.) For more information, see Partitioning and Locking.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Sasa Mladenovic on May 12, 2011
Good to know that you can do INSERT ... SELECT... UNION also. For example:

INSERT INTO tbl_temp1 (fld_id)
SELECT tbl_temp2.fld_order_id
SELECT tbl_temp3.fld_order_id
  Posted by Court Lukens on May 18, 2011
You only want to copy one piece of data from another table into your new entry. In this case my unique user_id generated from auto increment portion of another table.

INSERT INTO user_perms (user_id,user_name,pass_hash,require_dez,locked) VALUES ((select user_id from user_info where email=''),'jimbob','EMPTY','1','F');
  Posted by Devin Butts on May 20, 2011
I was receiving the "Column count doesn't match value count at row 1" error using the following code:

drop table if exists tempAttend;
create table tempAttend like Attendance;
insert into tempAttend select * from Attendance inner join Teams on Attendance.TeamLink=Teams.idx where Teams.idx=99;

It turns out that the problem was related to the "inner join". The * in the Select ends up retrieving all the fields from both tables. It therefore must be qualified such as:

insert into tempAttend select Attendance.* from Attendance inner join…

  Posted by Paul Jewell on May 7, 2012
Following on from Court's comment - I found that the following structure didn't work:

insert into tblA(fld1, fld2, fld3) values ((select f1, f2 from tblB, tblC where = 2 and = "test"), 'Field3 text');

The error message was: ERROR 1136 (21S01): Column count doesn't match value count at row 1

However, the following did work:

insert into tblA(fld1, fld2, fld3) values ((select f1 from tblB where = 2), (select f2 from tblCwhere = "test"), 'Field3 text');

  Posted by Michael John on November 10, 2014
Despite many years of SQL experience it is only in the past week I have started handing BLObs. Whilst Selecting them (for further processing) was not a problem Inserting was! The documentation I found was quite scant and, sometimes, incorrect. At one stage I was using two different syntaxes – one flor a single BLOb and another for two or more. Eventually I came down to the following two syntaxes that work for one to eight BLObs (the maximum I need to insert at one time). The coding is in Python under Linux.

To open the files:-

Doppler = open("/home/mjh/Documents//DemoData/MJH_Doppler", 'r').read()
CT_Scan = open("/home/mjh/Documents//DemoData/MJH_CT_Scan", 'r').read()

First method:-

sql = "Insert into Results (idResults, idClient, TestDateTime, Doppler, CT_Scan) \
Values (24, 8, '2014-11-07 09:33:30', %s, %s)"

cursor.execute(sql, ([Doppler, CT_Scan]))

Second Method:-

sql = "Insert into Results (idResults, idClient, TestDateTime, Doppler, CT_Scan) \
Values (26, 8, '2014-11-07 09:39:30', %(one)s, %(two)s)"

cursor.execute(sql, {'one' : Doppler, 'two' : CT_Scan})