MySQL INSERT INTO SELECT: Copy Query Results

Use INSERT INTO ... SELECT to add rows returned by a query to an existing table. Unlike INSERT ... VALUES, the rows come from a SELECT statement rather than from literal values. This is useful for copying or transforming data between tables.

MySQL INSERT INTO SELECT syntax

INSERT INTO target_table (target_column_1, target_column_2)
SELECT source_expression_1, source_expression_2
FROM source_table
[WHERE condition];

The target table must already exist. The SELECT list must return the same number of values as the target column list, in the corresponding order, and the values must be convertible to the target column types. Include the target column list so the mapping is explicit.

Copy selected rows into another table

The following example copies paid orders from a source table into an archive table. Run it in a test database:

CREATE TABLE order_source (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO order_source (order_id, customer_id, status, amount) VALUES
    (5001, 101, 'paid', 89.90),
    (5002, 102, 'pending', 44.00),
    (5003, 101, 'paid', 129.00);

CREATE TABLE paid_order_archive (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

Insert only the paid rows. The source query returns three columns, which match the three target columns:

INSERT INTO paid_order_archive (order_id, customer_id, amount)
SELECT order_id, customer_id, amount
FROM order_source
WHERE status = 'paid';

Check the archive table:

SELECT order_id, customer_id, amount
FROM paid_order_archive
ORDER BY order_id;

The query returns:

+----------+-------------+--------+
| order_id | customer_id | amount |
+----------+-------------+--------+
|     5001 |         101 |  89.90 |
|     5003 |         101 | 129.00 |
+----------+-------------+--------+

You can select from one or more source tables, join tables, and use conditions or aggregate expressions in the SELECT part. Only rows returned by that query are candidates for insertion.

Common mistakes and duplicate keys

• Column count mismatch: The SELECT list must return one value for every target column in the insert list.
• Wrong column order: Values from the SELECT list map to target columns from left to right. Alias expressions clearly when it helps explain the mapping.
• Target table does not exist: Create it first. Use CREATE TABLE ... SELECT when you want MySQL to create a new table from a query result.
• Duplicate keys: A plain INSERT INTO ... SELECT reports duplicate-key errors when selected rows conflict with a primary or unique key in the target. Use INSERT IGNORE to skip ignorable conflicts, or ON DUPLICATE KEY UPDATE when existing rows should be updated.

Summary

• Use INSERT INTO ... SELECT to populate an existing table from a query.
• Match the number, order, and compatible types of selected values to the target column list.
• Add a WHERE clause to control which source rows are copied.

For the complete syntax and rules, see the MySQL 8.4 Reference Manual: INSERT … SELECT.