MySQL INSERT ... SELECT: Production Guide

INSERT ... SELECT writes rows produced by a query directly into a target table. This guide starts with the core pattern and then covers the production concerns that usually determine whether the operation is safe: explicit column mapping, previewing the source query, duplicate keys, auto-increment behavior, transactions, locking, deterministic batching, verification, and rollback.

Use explicit target and source columns

INSERT INTO archived_orders (
    order_id,
    customer_id,
    total,
    archived_at
)
SELECT
    id,
    customer_id,
    total,
    CURRENT_TIMESTAMP
FROM orders
WHERE status = 'closed'
  AND created_at < '2025-01-01';

Target columns and selected expressions correspond by position, not by matching names. Explicit lists make conversions and schema drift reviewable.

Preview the SELECT before inserting

SELECT
    id,
    customer_id,
    total,
    CURRENT_TIMESTAMP AS archived_at
FROM orders
WHERE status = 'closed'
  AND created_at < '2025-01-01';

Check the source count, nullability, type/length compatibility, duplicate unique keys, and whether joins multiply rows. For a high-impact migration, preserve a deterministic record of which source IDs are supposed to move.

Transform and aggregate during the insert

INSERT INTO customer_summary (
    customer_id,
    order_count,
    lifetime_value
)
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS lifetime_value
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

The SELECT can contain joins, expressions, filters, grouping, and aggregation. The produced values still have to satisfy target data types, constraints, and uniqueness rules.

Auto-increment columns

If the target should generate new IDs, omit the auto-increment column:

INSERT INTO archived_orders (
    customer_id,
    total,
    created_at
)
SELECT
    customer_id,
    total,
    created_at
FROM orders
WHERE archived = 1;

MySQL 8.4 documents INSERT ... SELECT as a bulk-insert form for InnoDB auto-increment allocation because the final number of inserted rows is not necessarily known in advance. Concurrency behavior depends on innodb_autoinc_lock_mode and the workload.

Duplicate keys: fail, ignore, or update deliberately

Without a duplicate-handling clause, a duplicate primary/unique key is an error. INSERT IGNORE changes some errors into warnings and can skip or adjust rows:

INSERT IGNORE INTO archived_orders (
    order_id,
    customer_id,
    total
)
SELECT
    id,
    customer_id,
    total
FROM orders
WHERE archived = 1;

That can be appropriate for an explicitly idempotent load, but it can also hide data-quality problems. Inspect warnings and reconcile expected versus inserted rows.

ON DUPLICATE KEY UPDATE: do not copy stale syntax blindly

MySQL 8.4 supports ON DUPLICATE KEY UPDATE with INSERT ... SELECT. Current documentation also warns about older VALUES(column)-style references, so use syntax valid for your exact 8.x release. Tables with multiple unique indexes need extra caution because the duplicate that causes an update may not match your mental model of the “key.”

Affected rows are not always the source row count

For ON DUPLICATE KEY UPDATE, MySQL documents per-row affected-row semantics: an inserted row counts as 1, an existing row that is updated counts as 2, and an existing row assigned its current values counts as 0 unless client flags alter that behavior. Do not use one affected-row number as a universal equivalence to “rows selected.”

Transactions and rollback

START TRANSACTION;

INSERT INTO archived_orders (
    order_id,
    customer_id,
    total
)
SELECT
    id,
    customer_id,
    total
FROM orders
WHERE archived = 1;

SELECT ROW_COUNT() AS affected_rows;

-- Verification queries here.

ROLLBACK;

This is useful for a rehearsal or controlled production change when the involved tables/statements are transactional and the transaction size is acceptable. A huge transaction can retain locks, grow undo/redo work, and make recovery slower. DDL, nontransactional storage engines, and external side effects do not fit the same rollback assumption.

Locking is workload-specific

The lock footprint depends on isolation level, indexes, the source query plan, foreign keys, duplicate checks, whether source and target overlap, and auto-increment settings. “One SQL statement” does not mean “nonblocking.” Test representative data and concurrency.

Batch by a stable key, not an unordered drifting LIMIT

INSERT INTO archived_orders (order_id, customer_id, total)
SELECT id, customer_id, total
FROM orders
WHERE id > 100000
  AND id <= 110000
  AND archived = 1;

For a large migration, smaller commits can reduce blast radius. Record completed ranges and make the operation idempotent/resumable. Repeated unordered LIMIT batches can skip or duplicate work when the source changes between runs.

Avoid SELECT * for production migrations

-- Fragile when schemas drift:
INSERT INTO orders_backup
SELECT *
FROM orders;

Explicit columns communicate the mapping and are less likely to break when a new column is added to one side.

Verify before committing

Compare the previewed source set with the expected target delta.
Inspect warnings for IGNORE or upsert paths.
Reconcile counts and sums that should match.
Sample transformed values and null behavior.
Check unique/foreign-key constraints.
Confirm the application reads the resulting rows correctly.

Plan rollback before execution

If the change is too large to keep in one transaction, define a deterministic reversal key before starting: a migration batch ID, a controlled timestamp, or a captured/ranged set of source IDs. “Delete the new rows later” is not a recovery plan unless you can identify exactly which rows this run created or changed.

Run INSERT ... SELECT from Laravel

use Illuminate\Support\Facades\DB;

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

Laravel does not provide a dedicated fluent Query Builder method for this statement. Use a parameterized statement and never interpolate untrusted values into SQL. If the copy and a following delete must succeed together, use a database transaction only after confirming that the tables and operations are transactional and that the transaction size is acceptable.

MySQL INSERT ... SELECT: Syntax and Production Safety