The INSERT...SELECT Pattern in MySQL

INSERT INTO ... SELECT copies rows from one table (or query result) directly into another entirely within the database engine — genuinely faster than fetching rows into an application and looping through individual insert statements for the same result.

The basic syntax

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

The column lists on both sides must correspond in order and count (though not necessarily in name) — the first matching column from the SELECT populates the first listed column in INSERT, and so on down the list.

Why this is significantly faster than an application-level loop

// The slow, naive equivalent in application code
$oldOrders = DB::table('orders')->where('created_at', '<', '2024-01-01')->get();

foreach ($oldOrders as $order) {
    DB::table('archived_orders')->insert((array) $order);
}

The application-level loop pulls every matching row out of the database, transmits it over the network to the application, then sends it back as a separate insert query per row — INSERT...SELECT does the equivalent work entirely inside the database engine in one single operation, with no round-trip to the application at all for each individual row.

Copying with a computed or transformed value

INSERT INTO order_summaries (customer_id, order_count, total_spent)
SELECT customer_id, COUNT(*), SUM(total)
FROM orders
GROUP BY customer_id;

The SELECT side can include aggregate functions, computed expressions, or joins — anything a normal SELECT query can do, the results of which are inserted directly, not limited to a simple column-for-column copy.

Copying from a joined query

INSERT INTO customer_order_log (customer_name, order_id, order_total)
SELECT customers.name, orders.id, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.status = 'completed';

Running INSERT...SELECT from Laravel

DB::statement('
    INSERT INTO archived_orders (customer_id, total, created_at)
    SELECT customer_id, total, created_at
    FROM orders
    WHERE created_at < ?
', ['2024-01-01']);

Following the raw-SQL guidance covered elsewhere on this site, this needs to go through DB::statement() with proper parameter binding — Eloquent and the query builder have no first-class fluent method specifically for expressing an INSERT...SELECT statement.

Handling duplicate keys during the insert

INSERT INTO product_summary (product_id, total_sold)
SELECT product_id, SUM(quantity)
FROM order_items
GROUP BY product_id
ON DUPLICATE KEY UPDATE total_sold = VALUES(total_sold);

ON DUPLICATE KEY UPDATE, a MySQL-specific extension, updates the existing row instead of failing with a duplicate-key error when a row with the same unique/primary key already exists — genuinely useful for a summary table that needs periodic refreshing rather than only ever accepting brand-new rows.

Wrapping a large INSERT...SELECT in a transaction

DB::transaction(function () {
    DB::statement('INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < ?', ['2024-01-01']);
    DB::table('orders')->where('created_at', '<', '2024-01-01')->delete();
});

Following the database transaction guidance covered elsewhere on this site, wrapping the archive-then-delete pair in a transaction ensures both operations succeed or fail together — without it, a failure between the two statements could leave orders duplicated in both tables, or deleted from the original without ever having been successfully archived.