The target table of the
INSERT statement may appear
in the FROM clause of the
SELECT part of the query.
(This was not possible in some older versions of MySQL.)
However, you cannot insert into a table and select from the
same table in a subquery.

When selecting from and inserting into a table at the same
time, MySQL creates a temporary table to hold the rows from
the SELECT and then inserts
those rows into the target table. However, it remains true
that 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 B.5.6.2, “TEMPORARY Table Problems”).

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.

In the values part of ON DUPLICATE KEY
UPDATE, you can refer to columns in other tables, as
long as you do not use GROUP BY in the
SELECT part. One side effect is
that you must qualify nonunique column names in the values part.

The order in which rows are returned by a
SELECT statement with no
ORDER BY clause is not determined. 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; this can lead to inconsistencies between
them. To prevent this from occurring, you should always write
INSERT ... SELECT statements that are to be
replicated as INSERT ... SELECT ... ORDER BY
column. The choice of
column does not matter as long as the
same order for returning the rows is enforced on both the master
and the slave. See also
Section 17.4.1.16, “Replication and LIMIT”.

Due to this issue, beginning with MySQL 5.5.18,
INSERT ...
SELECT ON DUPLICATE KEY UPDATE and
INSERT IGNORE ...
SELECT statements are flagged as unsafe for
statement-based replication. With this change, such statements
produce a warning in the log when using statement-based mode and
are logged using the row-based format when using
MIXED mode. (Bug #11758262, Bug #50439)

An INSERT ... SELECT statement that acts on
partitioned tables using a storage engine such as
MyISAM that employs table-level
locks locks all partitions of the source and target tables. This
does not occur with tables using storage engines such as
InnoDB that employ row-level
locking. This issue is resolved in MySQL 5.6. See
Section 19.5.4, “Partitioning and Table-Level Locking”, for more
information.

If you want to insert a date field you have to do this in the format (YYYY-MM-DD). If you have a application that is not in this format (DD-MM-YYYY), you can use substring to correct this in your SQL statement.

Using INSERT INTO ... SELECT you can copy data from one database to another. You need the correct privileges for both.

Enter the source database, database1:use database1;

Then write to which fields in the destination database you want to copy to, database2:INSERT INTO database2.table1 (field1,field3,field9)SELECT table2.field3,table2.field1,table2.field4FROM table2;

The order of the selected fields and the inserted fields must match, so you enter the correct data. Before doing this, use "describe database2.table1" and "describe database1.table2", to make sure the new fields can hold the same kind of information.

Practical example:If you have a large number of WWW-links in one content management system, and you don't want to enter them again if you switch to another, this is the way to do it. Cheers!

NOTE: To copy data from one table to another inside just one database, remove the reference to database2 in the above INSERT... SELECT statement, like this:

CAVEAT: For moving of a small number of rows (<5) the INSERT ... SELECT query is *significantly* slower than inserting the rows individually.

However, when dealing with larger numbers of rows (>10), it is also significantly *faster* than inserting individually. It looks like a balance must be sought.

If you plan on executing a large number of these queries, you might want to check how many rows are to be inserted first before deciding how to go about inserting them; either with INSERT ... SELECT or individually.

Posted by
Thierry Coppey
on
December 29, 2004

If you want to copy a record from a table to another that is similar but not identical, you have to put all your values inside the select. For example:CREATE TABLE a (name VARCHAR(20), last VARCHAR(20));CREATE TABLE b (name VARCHAR(20), last VARCHAR(20), age TINYINT(3));If you want to copy Joe from a to b and add his age (23) do the followingINSERT INTO b SELECT name, last, 23 FROM a WHERE name="Joe";

Posted by
Julio Nobrega
on
June 29, 2005

Use REPLACE instead of INSERT if the SELECT returns more than one row with the same values and you are trying to insert on a column with a PRIMARY or an UNIQUE key.

I had to extract from a database some information related to particular records (an specific client), but based on a product purchased. So I reconstructed the tables on another database and issued some INSERT SELECT queries.

Example1) $lastids=mysql_fetch_array(mysql_query("SELECT Id_Inmueble FROM inmueble WHERE 1 ORDER BY Id_Inmueble DESC"));2) $lastid=$lastids["Id_Inmueble"]+1;3) mysql_query("INSERT INTO inmueble SELECT * FROM inmueble WHERE Id_Inmueble=".$Id." ON DUPLICATE KEY UPDATE Id_Inmueble=".$ultimoid) or die(mysql_error());

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.