MySQL Insert Select: Copy Rows from One Table to Another

Instead of a list of literal values, INSERT can take its rows from a SELECT. It is the standard way to copy, archive and transform data.

query.sql

SQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer VARCHAR(50) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    ordered_on DATE NOT NULL
);
INSERT INTO orders (customer, total, status, ordered_on) VALUES
    ('Maya', 680.00,  'delivered', '2025-11-02'),
    ('Maya', 1299.00, 'delivered', '2026-05-20'),
    ('Rohan', 145.00, 'shipped',   '2026-05-21'),
    ('Sana', 450.00,  'delivered', '2025-12-01'),
    ('Sana', 80.00,   'cancelled', '2026-06-05');

query.sql

SQL

CREATE TABLE orders_archive LIKE orders;

INSERT INTO orders_archive (id, customer, total, status, ordered_on)
SELECT id, customer, total, status, ordered_on
FROM orders
WHERE ordered_on < '2026-01-01';

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

query.sql

SQL

SELECT * FROM orders_archive ORDER BY id;

Output

OUTPUT

+----+----------+--------+-----------+------------+
| id | customer | total  | status    | ordered_on |
+----+----------+--------+-----------+------------+
|  1 | Maya     | 680.00 | delivered | 2025-11-02 |
|  4 | Sana     | 450.00 | delivered | 2025-12-01 |
+----+----------+--------+-----------+------------+
2 rows in set

How columns match

The columns of the SELECT are matched by position to the column list. Their names do not matter, only the order, count and compatible types:

query.sql

SQL

CREATE TABLE customer_totals (customer VARCHAR(50), total_spent DECIMAL(12,2));

INSERT INTO customer_totals (customer, total_spent)
SELECT customer, SUM(total)
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer;

SELECT * FROM customer_totals ORDER BY customer;

Output

OUTPUT

+----------+-------------+
| customer | total_spent |
+----------+-------------+
| Maya     |     1979.00 |
| Rohan    |      145.00 |
| Sana     |      450.00 |
+----------+-------------+
3 rows in set

You can use any SELECT here: filters, joins, aggregates, UNION, calculations.

Archive and delete

A common job: move old rows to an archive table. Do both steps in a transaction, so a failure cannot leave a copy without a delete, or a delete without a copy:

query.sql

SQL

START TRANSACTION;
INSERT INTO orders_archive (id, customer, total, status, ordered_on)
SELECT id, customer, total, status, ordered_on FROM orders WHERE ordered_on < '2026-01-01' AND id NOT IN (SELECT id FROM orders_archive);
DELETE FROM orders WHERE ordered_on < '2026-01-01';
COMMIT;
SELECT (SELECT COUNT(*) FROM orders) AS in_orders, (SELECT COUNT(*) FROM orders_archive) AS in_archive;

Output

OUTPUT

+-----------+------------+
| in_orders | in_archive |
+-----------+------------+
|         3 |          2 |
+-----------+------------+
1 row in set

On large tables, do it in chunks (LIMIT 5000 per round) so locks stay short (DELETE).

Inserting into the same table

You may select from the table you insert into. MySQL reads the rows into a temporary area first, so it does not loop forever:

query.sql

SQL

1
2
3

INSERT INTO orders (customer, total, status, ordered_on)
SELECT customer, total, 'duplicate-test', ordered_on FROM orders WHERE id = 3;
SELECT id, customer, status FROM orders WHERE status = 'duplicate-test';

Output

OUTPUT

+----+----------+----------------+
| id | customer | status         |
+----+----------+----------------+
|  6 | Rohan    | duplicate-test |
+----+----------+----------------+
1 row in set

Cautions

Always list the target columns and the source columns.
Check the row count before you run a big one: SELECT COUNT(*) FROM (the same select) AS t;
Big INSERT ... SELECT statements hold locks on the source rows. On a busy system, run them in batches.
If the SELECT produces a value that does not fit (too long, wrong type), strict mode fails the whole statement.

info

Standard SQL

INSERT INTO ... SELECT is standard SQL and works in every major database.