INSERT INTO SELECT in MySQL

Copying rows between tables

Often you need to copy rows from one table into another — archiving old data, moving records, or building a summary table. MySQL's INSERT INTO ... SELECT does this in a single statement, with the full power of SELECT to filter and transform.

INSERT INTO ... SELECT

INSERT INTO archive (id, name)
SELECT id, name FROM users;

The SELECT reads rows from users, and INSERT puts them into archive. No values are typed by hand — the data comes straight from the source table.

Copying only some rows

INSERT INTO archive (id, name)
SELECT id, name FROM users
WHERE active = 0;

Add a WHERE to the SELECT to copy only matching rows. Here only inactive users are archived. Any filter you can write in a SELECT works here.

Copying specific columns

INSERT INTO summary (customer, total)
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

The SELECT can pick specific columns and even aggregate. Here we build a summary table of total spending per customer. The selected columns must match the target columns in order and type.

Transforming while copying

INSERT INTO products_new (id, name, price)
SELECT id, UPPER(name), price * 1.1
FROM products;

Because it is a full SELECT, you can transform data as you copy — here names become uppercase and prices rise by 10%. This is handy for migrations and cleanups.

Creating a table from a query

CREATE TABLE archive AS
SELECT * FROM users WHERE active = 0;

CREATE TABLE ... AS SELECT makes a new table and fills it from the query in one step. Note it copies the data and column types, but not primary keys, indexes or constraints — add those afterwards if needed.

Common mistakes

• Mismatched columns. Selected columns must match the target list in order and type.
• Forgetting WHERE. Without it, all rows are copied.
• Expecting keys from CREATE TABLE AS. It copies data, not keys or indexes.
• Duplicate-key errors. Copying into a table with existing keys can clash — use IGNORE or filter.

Summary

• INSERT INTO ... SELECT copies rows from one table to another.
• Add WHERE to copy only matching rows.
• Select specific columns or aggregates; they must match the target.
• Transform data with expressions while copying.
• CREATE TABLE ... AS SELECT builds and fills a new table (no keys).

Next, add many rows with inserting multiple rows, or summarise with GROUP BY and HAVING.