Reader Stacks
Databases & APIs

Mastering MySQL's INSERT...SELECT for Copying Data Between Tables

INSERT...SELECT copies data between tables entirely inside the database, in one statement — genuinely faster than looping through PHP to read then re-insert each row.

Published
Jun 13, 2023
Updated
Aug 20, 2026
By
Reader Stacks Editorial

INSERT...SELECT copies data from one table into another entirely inside the database engine, in a single statement — genuinely more efficient than the common alternative of looping through rows in application code, reading each one, and issuing a separate insert per row.

The basic syntax

INSERT INTO archived_orders (id, customer_id, total, created_at)
SELECT id, customer_id, total, created_at
FROM orders
WHERE created_at < '2023-01-01';

The column lists on both sides need to match in count and compatible type, in the same order — MySQL doesn't match by column name across the two tables, only by position.

Copying an entire table's structure and data at once

INSERT INTO orders_backup
SELECT * FROM orders;

Using SELECT * only works cleanly when orders_backup already has an identical column structure to orders — for tables with even slightly different columns, listing explicit column names on both sides (as in the first example) avoids a column-count mismatch error.

Copying only rows matching a condition

INSERT INTO high_value_customers (customer_id, total_spent)
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 10000;

The SELECT half can be any valid query — including joins, aggregates, and GROUP BY/HAVING clauses, as shown here — not just a simple flat table copy.

Handling duplicate keys during the copy

INSERT INTO archived_orders (id, customer_id, total)
SELECT id, customer_id, total FROM orders WHERE archived = 1
ON DUPLICATE KEY UPDATE total = VALUES(total);

ON DUPLICATE KEY UPDATE combined with INSERT...SELECT gives an "insert or update" (upsert) pattern for a data copy operation that might be re-run against overlapping data, rather than failing outright on a duplicate primary key or unique constraint.

Using Laravel's query builder for the same pattern

DB::statement('
    INSERT INTO archived_orders (id, customer_id, total, created_at)
    SELECT id, customer_id, total, created_at FROM orders WHERE created_at < ?
', [$cutoffDate]);

Eloquent has no built-in fluent method for INSERT...SELECT specifically — dropping to DB::statement() with a parameterized raw query (using ? placeholders, as shown, to avoid SQL injection) is the standard way to run this pattern from within a Laravel application.

Why this beats a PHP loop for large data migrations

Fetching thousands of rows into PHP, looping through them, and inserting each one individually involves considerable overhead — network round-trips between PHP and the database, PHP memory for holding the fetched rows, and per-row insert overhead — all of which INSERT...SELECT avoids entirely by keeping the whole operation inside the database engine itself.

A genuine caution: this runs as one large transaction by default

For a very large copy operation, running it as one enormous statement can hold locks and consume transaction log space for an extended period — for a genuinely huge migration, batching the operation with a LIMIT and a loop of smaller INSERT...SELECT statements (each committing separately) is sometimes the more production-safe approach than one massive single statement.