SQLite Bulk INSERT with Variable Limits

SQLite-Specific Bulk INSERT

SQLite is a lightweight embedded database with specific limitations that affect bulk INSERT generation. The tool accounts for these constraints to produce compatible output.

SQLite Identifier Quoting

SQLite uses double quotes for identifiers (same as PostgreSQL):

INSERT INTO "users" ("id", "name", "email")
VALUES
  (1, 'Alice', 'alice@example.com'),
  (2, 'Bob', 'bob@example.com');

Variable Number Limit

SQLite's key constraint is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999. Each value in a bulk INSERT counts as one variable:

max_rows_per_insert = floor(999 / columns)

Columns	Max Rows per INSERT
5	199
10	99
15	66
20	49

Recommendation: Use batch size 50-100 for SQLite to stay safely within limits.

ON CONFLICT in SQLite

SQLite supports a subset of PostgreSQL's ON CONFLICT syntax:

INSERT INTO "users" ("id", "name", "email")
VALUES
  (1, 'Alice', 'alice@example.com')
ON CONFLICT ("id") DO NOTHING;

INSERT INTO "users" ("id", "name", "email")
VALUES
  (1, 'Alice Updated', 'alice.new@example.com')
ON CONFLICT ("id") DO UPDATE SET
  "name" = EXCLUDED."name",
  "email" = EXCLUDED."email";

SQLite-Specific Tips

1. Use transactions: SQLite is dramatically faster inside a transaction
2. WAL mode: Enable PRAGMA journal_mode=WAL for concurrent reads during imports
3. Batch conservatively: Stay well below the 999-variable limit
4. Disable synchronous: For bulk imports, PRAGMA synchronous=OFF speeds things up (but risks data loss on crash)