INSERT Forms, Defaults, Multi-Row Inserts, and Generated Keys

Learning outcomes

SQL Fundamentals introduced INSERT as the operation that adds rows. SQLite adds details that matter in real applications: omitted columns receive defaults, an exact INTEGER PRIMARY KEY can allocate a rowid, last_insert_rowid() belongs to one connection, and a multi-row statement is still one statement for normal ABORT-style constraint handling.

INSERT has three main shapes

Choose the form from the source of the data. VALUES is explicit row construction, INSERT ... SELECT copies/transforms rows produced by a query, and DEFAULT VALUES creates one row entirely from column defaults or NULL where permitted.

DROP TABLE IF EXISTS ingest_note;CREATE TABLE ingest_note (    note_id     INTEGER PRIMARY KEY,    device_code TEXT NOT NULL,    severity    INTEGER NOT NULL DEFAULT 1 CHECK(severity BETWEEN 1 AND 5),    status      TEXT NOT NULL DEFAULT 'open',    created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO ingest_note(device_code, severity)VALUES ('PUMP-007', 4);INSERT INTO ingest_note(device_code, severity) VALUES('FAN-014', 2),('SENS-003', 3);SELECT note_id, device_code, severity, status FROM ingest_note ORDER BY note_id;

The expected state is three rows. Because status was omitted, SQLite supplied 'open'. Because note_id was omitted and it is an exact INTEGER PRIMARY KEY, SQLite allocated integer rowids.

Omission and NULL are different requests

A default is used because a column was not supplied. Supplying SQL NULL is an explicit value request. If the column is NOT NULL, that explicit NULL is rejected rather than silently replaced by the default.

-- Uses severity DEFAULT 1:INSERT INTO ingest_note(device_code) VALUES ('VALVE-002');-- Fails: explicit NULL is not omission.INSERT INTO ingest_note(device_code, severity)VALUES ('MOTOR-011', NULL);

This distinction is important at language-driver boundaries. An absent JSON property, a Python None, and an omitted SQL column are three different application states unless you deliberately map them to the same meaning.

INSERT ... SELECT moves rows produced by a query

This form is the bridge from staging to final data. It is safer than reconstructing rows in application loops when the transformation can be expressed as SQL and protected by one transaction.

DROP TABLE IF EXISTS note_stage;CREATE TABLE note_stage(device_code TEXT, severity_text TEXT);INSERT INTO note_stage VALUES('PUMP-021','2'),('FAN-030','5');INSERT INTO ingest_note(device_code, severity)SELECT trim(device_code), CAST(severity_text AS INTEGER)FROM note_stageWHERE device_code IS NOT NULL;SELECT device_code, severity, typeof(severity)FROM ingest_noteWHERE device_code IN ('PUMP-021','FAN-030');

The SELECT controls which rows are eligible and how values are transformed; the target table still applies its own NOT NULL, CHECK, UNIQUE, and foreign-key rules.

DEFAULT VALUES creates one default-driven row

This form is useful for settings/state tables where every column has a meaningful default. It is not a general substitute for omitting selected columns in a normal VALUES insert.

DROP TABLE IF EXISTS run_state;CREATE TABLE run_state (    run_id INTEGER PRIMARY KEY,    state TEXT NOT NULL DEFAULT 'queued',    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO run_state DEFAULT VALUES;SELECT run_id, state, created_at FROM run_state;

The row gets an allocated run_id plus both declared defaults.

Generated keys: ask SQLite, never guess max(id)+1

In an ordinary rowid table, an exact INTEGER PRIMARY KEY aliases the rowid. Omitting it—or supplying NULL—asks SQLite to choose the rowid. The SQL function last_insert_rowid() reports the most recent successful rowid-table INSERT on the current connection.

INSERT INTO ingest_note(device_code, severity)VALUES ('PUMP-099', 3);SELECT last_insert_rowid() AS generated_note_id;

SELECT max(note_id)+1 is the wrong mechanism: another writer can race with that guess, deleted IDs may create gaps, and key allocation is already SQLite's job. In application code, use the driver's generated-key API on the same connection or use RETURNING note_id when supported by your runtime.

Multi-row INSERT and statement atomicity

With SQLite's normal ABORT conflict behavior, a constraint failure backs out prior changes from the same statement. That is different from issuing several independent INSERT statements one by one.

SELECT count(*) AS before_count FROM ingest_note;-- The middle row violates CHECK. The statement fails.INSERT INTO ingest_note(device_code, severity) VALUES('BATCH-A', 2),('BATCH-B', 9),('BATCH-C', 4);SELECT count(*) AS after_count FROM ingest_note;SELECT * FROM ingest_note WHERE device_code LIKE 'BATCH-%';

Expected result: the INSERT raises a CHECK error and no BATCH-% row remains from that statement. Chapter 5 showed that explicit conflict algorithms such as FAIL or IGNORE can change failure scope, so do not generalize this statement-level result to every conflict policy.

Batch-ingestion lab

Use a disposable table and make validation visible. First ingest a valid batch. Then try a batch with one invalid row. Finally inspect counts and generated IDs.

DROP TABLE IF EXISTS batch_event;CREATE TABLE batch_event (    event_id INTEGER PRIMARY KEY,    source_key TEXT NOT NULL UNIQUE,    severity INTEGER NOT NULL DEFAULT 1 CHECK(severity BETWEEN 1 AND 5),    received_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);INSERT INTO batch_event(source_key, severity) VALUES('api-001',2),('api-002',4),('api-003',1);SELECT count(*) AS valid_count FROM batch_event; -- 3-- Run separately; should fail and add zero rows:INSERT INTO batch_event(source_key, severity) VALUES('api-004',3),('api-005',8),('api-006',2);SELECT count(*) AS final_count FROM batch_event; -- still 3SELECT event_id, source_key, severity FROM batch_event ORDER BY event_id;

The durable state should contain only the first three rows. The generated IDs are evidence supplied by SQLite, not values precomputed by the caller.

Production judgment and bridge

Prefer explicit target column lists, bound parameters, database constraints, and a single transaction for a logical ingestion unit. Generated IDs should come from SQLite or the driver—not from guesses. The next lesson applies the same discipline to UPDATE, where the greatest operational risk is often not syntax but selecting too many rows.

Authoritative references

• SQLite — INSERT.
• SQLite — last_insert_rowid.
• SQLite — ROWID Tables.
• SQLite — Transaction Control.