INSERT and INSERT … SELECT

Why loading is where data quality is won or lost

Every table starts empty. INSERT is how rows get in, and the form you choose decides whether your warehouse stays clean. INSERT ... VALUES writes literal rows you type out, which is how you seed small reference tables (status codes, country lists). INSERT ... SELECT writes rows read from another table and transforms them on the way in. That second form is the entire "T" in ELT: you read raw, cast, trim, and rename inside the SELECT, then land a clean result in a model table. One statement, no application code, no temp files, all inside the database.

The mental model: positional mapping

Both forms fill a named target column list, and neither matches on column names. They map by position: the first output expression fills the first named column, the second fills the second, and so on. The SELECT column names do not have to match the target names, only the order matters. That is why the target column list is not decoration. It is the contract that says which slot each value lands in.

Worked example

raw_product holds dirty text (padded SKUs, prices as strings, some NULL ids). Load a clean dimension straight from it:

-- raw_product:
--   prod_id | sku     | prod_name   | price_txt
--   '10'    | ' a12 ' | ' Widget '  | '1999'
--   NULL    | 'x9'    | 'junk'      | '0'
--   '11'    | 'b7'    | 'Gadget'    | '4950'

INSERT INTO dim_product (product_id, sku, name, unit_price_cents)
SELECT CAST(prod_id AS INTEGER),
       UPPER(TRIM(sku)),
       TRIM(prod_name),
       CAST(price_txt AS INTEGER)
FROM raw_product
WHERE prod_id IS NOT NULL;   -- drop junk at the boundary

-- dim_product now holds:
--   product_id | sku | name   | unit_price_cents
--   10         | A12 | Widget | 1999
--   11         | B7  | Gadget | 4950

Every surviving row is cast, trimmed, and inserted as one set. The NULL-id row never lands.

Seeding with multi-row VALUES

For small static data, one statement carries many rows:

INSERT INTO dim_status (code, label) VALUES
    ('paid', 'Paid'),
    ('shipped', 'Shipped'),
    ('cancelled', 'Cancelled');

Pitfalls

• Skipping the column list. INSERT INTO t SELECT ... with no list binds to the table's physical column order. The day someone adds or reorders a column in the DDL, values silently slide into the wrong slots. Always name the target columns.
• No boundary WHERE. Forget it and NULL-keyed junk rows load straight into a clean model. Filter garbage at load time, not later.
• Column count mismatch. If the SELECT returns a different number of expressions than the target list names, the statement errors instead of loading. That is a feature: it catches the mistake at the boundary.

Interview nuance: to load one row per key (the dedup the Practice asks for), SELECT DISTINCT customer_id, email, country_code is the usual first reach, but DISTINCT collapses on the entire projected row, not on customer_id alone. It works here only because you clean the columns first, so a customer's repeated feed lines become byte-identical and fold into one. If a single customer_id carried two different emails, DISTINCT would keep both rows and you would still have duplicate keys. True one-row-per-key needs GROUP BY customer_id with an aggregate, or ROW_NUMBER() OVER (PARTITION BY customer_id ...) filtered to = 1.

Execution mode: you write a multi-statement script against a fresh in-memory SQLite database. Hidden assertions then check row counts, cleaned values, and column types.