INSERT — All the Shapes

The most common write

INSERT looks simple but has several shapes worth knowing:

Positional — INSERT INTO t VALUES (...). Column order is whatever CREATE TABLE declared. Brittle.
Named columns — INSERT INTO t(a, b) VALUES (...). Always use this; survives schema changes.
Multi-row — INSERT INTO t(a) VALUES (1), (2), (3). One transaction, much faster than N single-row inserts.
INSERT ... SELECT — INSERT INTO t(a) SELECT x FROM other. Bulk copy from another table.
RETURNING (3.35+) — INSERT ... RETURNING id. Get back generated values without a second round-trip.
UPSERT (covered later) — INSERT ... ON CONFLICT(...) DO UPDATE.

Tip: For a Python loop inserting thousands of rows, wrap the loop in a single transaction (conn.execute('BEGIN') ... conn.commit()) or use executemany. Default 'one transaction per statement' is the #1 reason people think SQLite is slow.

Code

Five shapes of INSERT·sql

-- Positional (avoid)
INSERT INTO users VALUES (1, 'a@x.com', 'alice', NULL, 'member', datetime('now'));

-- Named columns (preferred)
INSERT INTO users(email, username) VALUES ('a@x.com', 'alice');

-- Multi-row
INSERT INTO users(email, username) VALUES
  ('a@x.com', 'alice'),
  ('b@x.com', 'bob'),
  ('c@x.com', 'carol');

-- INSERT ... SELECT
INSERT INTO users_archive(email, username)
SELECT email, username FROM users WHERE archived = 1;

-- RETURNING (3.35+)
INSERT INTO users(email, username) VALUES ('d@x.com', 'dave')
RETURNING id, created_at;

Python — fast bulk insert with executemany·python

import sqlite3

rows = [(f'user{i}@x.com', f'user{i}') for i in range(10000)]

conn = sqlite3.connect('demo.db')
with conn:                              # transaction context
    conn.executemany(
        'INSERT INTO users(email, username) VALUES (?, ?)',
        rows,
    )
# 10k rows committed in one transaction — usually <100 ms

Exercise

Time three approaches for inserting 10,000 rows into a small table from Python: (1) one INSERT per loop iteration with autocommit, (2) one INSERT per loop iteration inside a single transaction, (3) executemany inside a single transaction. Measure with time.perf_counter and report the gap. Explain in one paragraph why the first one is dramatically slower.

INSERT — All the Shapes

The most common write

Code

External links

Exercise

Progress

Comments 0