INSERT SELECT

Definition

INSERT ... SELECT inserts rows into a table using the RESULT of a SELECT query as the source data, instead of a literal VALUES list — it's how you copy or derive data from one place in the database into another, entirely in SQL, with no data ever leaving the database.

How It Works

sql
INSERT INTO high_performers (id, name, salary)
SELECT id, name, salary
FROM employees
WHERE salary >= 80000;

This copies every employee earning at least 80,000 into a separate high_performers table, in one statement — no application code loop, no data round-tripping to a client and back. The SELECT's column list and the target table's column list must line up positionally (same count, compatible types), exactly like a UNION's union-compatibility requirement (7.4).

Edge Cases and Pitfalls

• INSERT ... SELECT is far more efficient than reading rows out with a SELECT, then looping in application code to INSERT them back one at a time — the entire operation stays inside the database engine, avoiding network round trips entirely.
• If the SELECT and the target table's columns don't line up in count or type, the statement fails — just like any other column-count mismatch (see Union, 7.4).
• A useful, common pattern: archiving old rows into a history table (INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < ...) followed by a DELETE of those same rows from the original table — though be careful to run both inside one transaction (11.11) so a failure between the two steps can't leave data duplicated or lost.

Key Takeaways

• INSERT ... SELECT populates a table from a query's result set, entirely inside the database.
• It's dramatically more efficient than a select-then-loop-insert pattern in application code.
• Source and destination column lists must be compatible in count and type, just like Union.