INSERT...SELECT in MySQL: Copying Rows Between Tables

Moving or copying a set of rows from one table into another — archiving old orders, seeding a table from an existing one, deduplicating into a summary table — doesn't need to pull the data out into application code at all. MySQL's INSERT ... SELECT syntax performs the entire copy as one statement, executed entirely within the database.

1. Basic syntax

INSERT INTO archived_orders (customer_id, total, created_at)
SELECT customer_id, total, created_at
FROM orders
WHERE status = 'completed' AND created_at < '2023-01-01';

The column list after INSERT INTO and the columns selected by the SELECT need to match in count and be in a compatible order — they don't need identical names, since MySQL maps them positionally, not by name.

2. Why this beats a fetch-then-insert loop

-- The slow alternative, done from application code:
-- 1. SELECT the matching rows
-- 2. Loop over each one in PHP
-- 3. Run a separate INSERT for every single row

A loop like that means one query per row, plus the overhead of transferring every row's data out to the application and back in again — for anything beyond a handful of rows, this is measurably slower than INSERT ... SELECT, which never leaves the database at all and inserts the entire matching set in one operation.

3. Copying into a table with an auto-increment ID

INSERT INTO archived_orders (customer_id, total, created_at)
SELECT customer_id, total, created_at
FROM orders
WHERE status = 'completed';

Leaving the auto-increment id column out of both the column list and the SELECT lets the destination table generate fresh IDs for the copied rows automatically — this is usually the correct approach when the destination table's IDs are meant to be independent of the source table's.

4. Adding literal or computed values alongside selected columns

INSERT INTO archived_orders (customer_id, total, created_at, archived_at)
SELECT customer_id, total, created_at, NOW()
FROM orders
WHERE status = 'completed';

The SELECT portion isn't limited to plain column references — a literal value (like NOW() here, recording when the archive operation ran) can be included as one of the selected "columns," letting the copy add contextual data the source table doesn't have at all.

5. Handling duplicate keys during the copy

INSERT IGNORE INTO archived_orders (id, customer_id, total)
SELECT id, customer_id, total
FROM orders
WHERE status = 'completed';

INSERT IGNORE silently skips any row that would violate a unique key or primary key constraint in the destination table, rather than the whole statement failing on the first conflict — useful for a repeatable archive job that might run more than once over overlapping data, though it also means any real data problem causing a conflict gets silently swallowed rather than raised as an error, which is worth being deliberate about.

6. Running this from Laravel with a raw statement

use Illuminate\Support\Facades\DB;

DB::statement("
    INSERT INTO archived_orders (customer_id, total, created_at)
    SELECT customer_id, total, created_at
    FROM orders
    WHERE status = 'completed' AND created_at < ?
", ['2023-01-01']);

Eloquent doesn't have a dedicated fluent method for INSERT ... SELECT specifically — DB::statement() with parameter binding (as shown, using ? placeholders rather than string-interpolating the date) is the standard way to run this pattern from Laravel while still avoiding SQL injection risk.

7. When to use this vs. a proper foreign-key relationship instead

INSERT ... SELECT is the right tool for a genuine one-time or periodic copy — archiving, seeding, denormalizing for a report. For data that should stay permanently linked and in sync going forward, a real foreign-key relationship (and querying across it with a JOIN) is the better long-term design; copying rows creates an independent snapshot that won't reflect later changes to the original data.