🔀INSERT INTO … SELECT

Query results become rows

This form composes a read with a write: the SELECT projection must align with the insert column list by position and compatible types. It is the workhorse for promoting cleansed staging data, building reporting slices, and backfilling derived tables, usually inside an explicit transaction so validation and insert succeed or roll back together.

SQL

Promote validated staging rows

INSERT INTO core.experiments (experiment_id, title, owner_id)
SELECT experiment_id, title, owner_id
FROM ingest.experiments_staging
WHERE validation_status = 'OK'
  AND experiment_id NOT IN (SELECT experiment_id FROM core.experiments);

✓Illustrative result set

rows_inserted
128

NOT IN (subquery) with nullable keys

If the subquery can produce NULL in the compared column, classical SQL makes NOT IN behave like “unknown” for many outer rows, you insert nothing or everything wrong with no obvious syntax error.

NOT EXISTS or anti-join with null guards

Prefer WHERE NOT EXISTS (SELECT 1 FROM core.experiments e WHERE e.experiment_id = s.experiment_id) when keys can be nullable, or inner-filter NULLs away first.

📐Grain checks before you commit

Joins in the SELECT multiply rows, always compare COUNT(*) from the source query to rows inserted.

A forgotten one-to-many join turns a “load 128 experiments” job into thousands of duplicate keys or constraint violations. Dry-run with SELECT COUNT(*) and spot-check keys before swapping in INSERT.

SQL

Column alignment and casts

INSERT INTO reporting.fact_reads (run_key, read_count, load_batch_id)
SELECT run_id::text, read_count, 20260412
FROM warehouse.seq_runs
WHERE load_ts > TIMESTAMP '2026-04-01';

✓Illustrative result set

rows_inserted
5400

⚡ Advanced / dialect note

For very large selects, batch by key ranges or dates, consider deferring nonessential secondary indexes during controlled loads, or use native bulk utilities (COPY, bcp) when raw throughput matters more than standard insert triggers.

Operational hygiene

Chunk work so a single transaction does not hold millions of locks through a replica lag spike; watch binary log / WAL growth during overnight pipelines.

TL;DR

INSERT INTO … SELECT … copies rows from a query into a destination table in one statement, ideal for ETL staging and rebuilds. It inherits SELECT semantics (joins, filters) and applies destination constraints row by row. Watch locking, transaction size, and identity/sequence collisions; use explicit column lists to stay safe when the destination schema evolves.