Runnable SQLite Docs: INSERT

Q: How do I insert a row in SQLite?

Use INSERT INTO table (col1, col2) VALUES (val1, val2);. Listing the columns is optional but strongly recommended — it keeps the statement working even if the table later gains a new column. Without a column list, you must supply a value for every column in declaration order.

Q: How do I insert multiple rows at once in SQLite?

Put several parenthesised tuples after VALUES, separated by commas: INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);. For real bulk loads (thousands of rows), wrap the inserts in a single transaction with BEGIN and COMMIT — that's where the speedup comes from, not the multi-row syntax itself.

Q: What does INSERT OR IGNORE do in SQLite?

INSERT OR IGNORE skips rows that would violate a UNIQUE, PRIMARY KEY, or NOT NULL constraint instead of raising an error. The conflicting row is silently dropped and the rest of the statement continues. Use it when you want 'insert if not already there' behaviour without a separate existence check.

Q: Why do I get 'UNIQUE constraint failed' on INSERT?

SQLite found an existing row with the same value in a UNIQUE or PRIMARY KEY column. Either the value really is a duplicate, or you're re-running a seed script. Switch to INSERT OR IGNORE to skip duplicates, INSERT OR REPLACE to overwrite them, or use ON CONFLICT ... DO UPDATE (upsert) for finer control.

INSERT Adds Rows to a Table

INSERT is the statement you use to put new rows into a table. The shape is short and predictable:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT,
    year   INTEGER
);

INSERT INTO books (title, author, year)
VALUES ('The Left Hand of Darkness', 'Ursula K. Le Guin', 1969);

SELECT * FROM books;

Three parts to notice:

INSERT INTO books — the target table.
(title, author, year) — the columns you're providing values for.
VALUES (...) — the values, in the same order as the column list.

id isn't in the column list, so SQLite assigns it automatically (it's an INTEGER PRIMARY KEY, which gets the rowid). Any column you skip falls back to its default value, or NULL if there isn't one.

Always List Your Columns

You can omit the column list and provide values for every column in declaration order:

-- Works, but fragile:
INSERT INTO books VALUES (NULL, 'Dune', 'Frank Herbert', 1965);

Don't. The moment someone adds a column to books, every statement like this breaks or starts inserting values into the wrong column. Spell out the columns:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT,
    year   INTEGER
);

INSERT INTO books (title, author, year)
VALUES ('Dune', 'Frank Herbert', 1965);

SELECT id, title, year FROM books;

Explicit column lists are a form of documentation — they make the statement readable on its own, without hunting for the table definition.

Inserting Multiple Rows

You can insert several rows in a single statement by listing more value tuples:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    author TEXT,
    year   INTEGER
);

INSERT INTO books (title, author, year) VALUES
    ('Dune', 'Frank Herbert', 1965),
    ('Neuromancer', 'William Gibson', 1984),
    ('Hyperion', 'Dan Simmons', 1989);

SELECT count(*) AS total FROM books;

This is cleaner than three separate INSERT statements, and SQLite treats the whole thing as one statement. The real performance win for bulk loads, though, comes from wrapping inserts in a transaction — more on that next.

Bulk Inserts: Wrap Them in a Transaction

By default, every INSERT is its own transaction. SQLite does an fsync at the end of each one, which is what makes naive loops slow — not the inserts themselves.

Group them:

CREATE TABLE numbers (n INTEGER);

BEGIN;
INSERT INTO numbers (n) VALUES (1);
INSERT INTO numbers (n) VALUES (2);
INSERT INTO numbers (n) VALUES (3);
INSERT INTO numbers (n) VALUES (4);
INSERT INTO numbers (n) VALUES (5);
COMMIT;

SELECT count(*) AS total, sum(n) AS total_sum FROM numbers;

One fsync instead of five. For thousands of rows the difference can be two or three orders of magnitude. If anything inside fails, ROLLBACK undoes the whole batch.

This pattern is the bulk-insert recipe. Use it whether you're calling SQLite from Python, Node, Rust — wrap your loop in BEGIN / COMMIT.

INSERT ... SELECT: Copying From Another Table

You can populate a table from a query instead of literal values:

CREATE TABLE books (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    year   INTEGER
);
INSERT INTO books (title, year) VALUES
    ('Dune', 1965), ('Neuromancer', 1984), ('Hyperion', 1989);

CREATE TABLE classics (title TEXT, year INTEGER);

INSERT INTO classics (title, year)
SELECT title, year FROM books WHERE year < 1980;

SELECT * FROM classics;

The columns from the SELECT are matched by position to the column list of the INSERT. Names don't have to match — order does. This is the standard way to archive rows, build reporting tables, or copy a subset of data during a migration.

DEFAULT VALUES and Skipped Columns

If a column has a DEFAULT clause, you can leave it out of the column list and SQLite fills in the default:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('signup');
INSERT INTO events (name) VALUES ('login');

SELECT * FROM events;

created_at gets the current timestamp because we didn't supply one. If you want a row made entirely of defaults — useful for placeholder rows — use the DEFAULT VALUES form:

CREATE TABLE counters (
    id    INTEGER PRIMARY KEY,
    value INTEGER DEFAULT 0
);

INSERT INTO counters DEFAULT VALUES;
INSERT INTO counters DEFAULT VALUES;

SELECT * FROM counters;

Two new rows, both with value = 0 and auto-assigned ids.

INSERT OR IGNORE: Skip Duplicates

When a row would violate a UNIQUE or PRIMARY KEY constraint, the default behaviour is to abort the statement with an error:

Error: UNIQUE constraint failed: users.email

INSERT OR IGNORE swaps that for "silently skip the offending row":

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

INSERT INTO users (email) VALUES ('ada@example.com');

INSERT OR IGNORE INTO users (email) VALUES
    ('ada@example.com'),    -- duplicate, skipped
    ('boris@example.com'),  -- inserted
    ('rosa@example.com');   -- inserted

SELECT * FROM users;

Three rows survive. The duplicate is dropped without error. This is the idiomatic SQLite way to express "insert if not exists" for simple seed data — no separate SELECT to check first, no exception handling.

INSERT OR REPLACE: Overwrite Duplicates

INSERT OR REPLACE deletes the conflicting row and inserts the new one in its place:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');

INSERT OR REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

SELECT * FROM settings;

Watch out for one thing: REPLACE is DELETE + INSERT, not UPDATE. If the deleted row had foreign keys pointing at it with ON DELETE CASCADE, those children get deleted too. And any column you don't list in the new INSERT resets to its default — it doesn't keep the old value.

For most "update if exists, insert if not" cases, you actually want a real upsert with ON CONFLICT ... DO UPDATE. That's covered on its own page.

A Quick Recap

INSERT INTO table (cols) VALUES (...) — the basic form. Always list columns.
Multi-row inserts use comma-separated tuples after VALUES.
For real bulk loads, wrap inserts in BEGIN / COMMIT.
INSERT INTO ... SELECT ... copies rows from a query.
DEFAULT VALUES makes a row from defaults alone; skipped columns fall back to their defaults too.
INSERT OR IGNORE skips conflicting rows; INSERT OR REPLACE overwrites them (via delete + insert).

Next: UPDATE

Inserting rows is half the story. The other half is changing rows that already exist — bumping a counter, fixing a typo, marking an order as shipped. That's UPDATE, and it has its own set of habits worth getting right (especially the WHERE clause). Coming up next.

Frequently Asked Questions

How do I insert a row in SQLite?

Use INSERT INTO table (col1, col2) VALUES (val1, val2);. Listing the columns is optional but strongly recommended — it keeps the statement working even if the table later gains a new column. Without a column list, you must supply a value for every column in declaration order.

How do I insert multiple rows at once in SQLite?

Put several parenthesised tuples after VALUES, separated by commas: INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);. For real bulk loads (thousands of rows), wrap the inserts in a single transaction with BEGIN and COMMIT — that's where the speedup comes from, not the multi-row syntax itself.

What does INSERT OR IGNORE do in SQLite?

INSERT OR IGNORE skips rows that would violate a UNIQUE, PRIMARY KEY, or NOT NULL constraint instead of raising an error. The conflicting row is silently dropped and the rest of the statement continues. Use it when you want 'insert if not already there' behaviour without a separate existence check.

Why do I get 'UNIQUE constraint failed' on INSERT?

SQLite found an existing row with the same value in a UNIQUE or PRIMARY KEY column. Either the value really is a duplicate, or you're re-running a seed script. Switch to INSERT OR IGNORE to skip duplicates, INSERT OR REPLACE to overwrite them, or use ON CONFLICT ... DO UPDATE (upsert) for finer control.

Related concepts

SQLite INSERT: Adding Rows, Bulk Inserts, and OR IGNORE