Introduction

MySQL can copy rows from one table into another without exporting data or looping in application code. The core tool is INSERT INTO ... SELECT, which combines a read query and a write query into one statement. Once you understand column matching and duplicate handling, it becomes a reliable pattern for archiving, reporting, and one-time data migrations.

Using INSERT INTO ... SELECT

The safest form always lists target columns explicitly. That makes the statement resilient if the table schema changes later and avoids accidental column order mismatches.

1CREATE TABLE orders (
2    id INT PRIMARY KEY,
3    customer_id INT NOT NULL,
4    total_amount DECIMAL(10, 2) NOT NULL,
5    status VARCHAR(20) NOT NULL,
6    created_at DATETIME NOT NULL
7);
8
9CREATE TABLE archived_orders (
10    order_id INT PRIMARY KEY,
11    customer_id INT NOT NULL,
12    total_amount DECIMAL(10, 2) NOT NULL,
13    archived_at DATETIME NOT NULL
14);
15
16INSERT INTO archived_orders (order_id, customer_id, total_amount, archived_at)
17SELECT id, customer_id, total_amount, NOW()
18FROM orders
19WHERE status = 'completed';

In this example, the SELECT does not need to return identical column names. It only needs to return values in the same order as the target column list. The fourth value comes from NOW(), which means you can add metadata during the copy instead of storing a literal value in the source table.

Filtering, transforming, and aggregating rows

The SELECT side can be as simple or as rich as any normal query. You can join tables, calculate new values, or summarize many source rows into one destination row.

1CREATE TABLE monthly_customer_totals (
2    month_start DATE NOT NULL,
3    customer_id INT NOT NULL,
4    total_amount DECIMAL(10, 2) NOT NULL,
5    PRIMARY KEY (month_start, customer_id)
6);
7
8INSERT INTO monthly_customer_totals (month_start, customer_id, total_amount)
9SELECT DATE(created_at - INTERVAL (DAYOFMONTH(created_at) - 1) DAY),
10       customer_id,
11       SUM(total_amount)
12FROM orders
13WHERE created_at >= '2025-01-01'
14  AND created_at < '2025-02-01'
15  AND status = 'completed'
16GROUP BY DATE(created_at - INTERVAL (DAYOFMONTH(created_at) - 1) DAY),
17         customer_id;

This version is useful for building reporting tables. The important detail is that the GROUP BY columns match the non-aggregated expressions in the SELECT. If they do not, MySQL may reject the query in strict SQL modes or return ambiguous results in permissive modes.

You can also move data between tables with different shapes. For example, a source table may store one broad transaction record while the target table stores only the subset needed for analysis. INSERT INTO ... SELECT is often simpler and faster than fetching rows into an application and pushing them back one at a time.

Handling duplicates and preserving consistency

If the target table has a primary key or unique index, duplicate rows can fail the insert. That is often correct, but sometimes you want a different behavior:

• Use INSERT IGNORE to skip rows that would violate a unique constraint.
• Use ON DUPLICATE KEY UPDATE to turn the insert into an upsert.
• Use a transaction when the copy is one step in a larger workflow.

INSERT INTO monthly_customer_totals (month_start, customer_id, total_amount)
SELECT DATE('2025-02-01'), 42, 125.00
ON DUPLICATE KEY UPDATE total_amount = VALUES(total_amount);

For large copies, wrap related statements in a transaction so either all dependent changes succeed or all of them roll back. Also think about indexes. A broad INSERT INTO ... SELECT can lock rows, fill undo logs, and run for a long time if the filter is weak or the source table lacks supporting indexes.

Common Pitfalls

The most common mistake is omitting the target column list. If the schema changes, old code may silently insert values into the wrong columns or fail with a confusing type error.

Another frequent problem is type incompatibility. A VARCHAR source column may copy into an integer column only if the values are convertible. When they are not, you can get truncated data, warnings, or full statement failure depending on SQL mode.

Duplicates also surprise people. Copying historical data into an archive table often works once, then fails on the second run because the primary keys already exist. Decide up front whether you want strict failure, skip behavior, or an upsert.

Finally, avoid copying huge tables without a filter, transaction plan, or rollback strategy. A bulk insert that touches millions of rows can stress production systems. Test the query with the SELECT by itself first, confirm row counts, and then run the insert during an appropriate maintenance window if necessary.

Summary

• Use INSERT INTO ... SELECT when data should move directly inside MySQL.
• Always list target columns explicitly to avoid column order mistakes.
• The SELECT can filter, join, transform, and aggregate before inserting.
• Plan for duplicates with INSERT IGNORE or ON DUPLICATE KEY UPDATE when needed.
• Validate the SELECT alone first, especially for large or production-facing copies.