Every database starts out empty. Before you can query data, sort it, filter it, or build reports out of it, you have to actually put something in there. That’s the job of INSERT. It sounds simple on the surface — and honestly, the basic form is simple — but INSERT in SQLite has more depth than most beginners realize, from handling conflicts gracefully to inserting data from other queries entirely. I’m going to walk through all of it in this guide, using practical examples you can run yourself.
The Basic Syntax
The simplest form of INSERT looks like this:
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
You name the table, list the columns you’re providing values for, and then supply those values in the same order. Let’s set up a table to work with throughout this article:
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
genre TEXT,
price REAL,
in_stock INTEGER DEFAULT 1
);
Now let’s insert a single row:
INSERT INTO books (title, author, genre, price, in_stock)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 'Fantasy', 12.99, 1);
Notice I didn’t provide a value for id. That’s because it’s an INTEGER PRIMARY KEY, and in SQLite, that column automatically acts like an auto-incrementing field. SQLite assigns the next available integer for you.
Omitting the Column List
If you’re providing values for every single column in the table, in the exact order the table was defined, you can skip the column list entirely:
INSERT INTO books VALUES (NULL, '1984', 'George Orwell', 'Dystopian', 9.99, 1);
I used NULL for the id here, which tells SQLite to auto-generate it since it’s a primary key. That said, I’d caution against relying on this shorthand in real projects. It’s fragile — if someone adds a new column to the table later, or reorders columns, this statement silently breaks or inserts data into the wrong places. Explicit column lists are safer and more self-documenting, even though they require a bit more typing.
Inserting Multiple Rows at Once
You don’t have to write a separate INSERT statement for every row. SQLite lets you batch multiple rows into a single statement:
INSERT INTO books (title, author, genre, price, in_stock) VALUES
('Dune', 'Frank Herbert', 'Science Fiction', 14.50, 1),
('Brave New World', 'Aldous Huxley', 'Dystopian', 10.25, 1),
('The Name of the Wind', 'Patrick Rothfuss', 'Fantasy', 13.75, 0),
('Neuromancer', 'William Gibson', 'Science Fiction', 11.00, 1);
This is significantly faster than running four separate INSERT statements, especially when you wrap it in a transaction, which I’ll cover shortly. When I’m loading seed data or test fixtures, this is always my preferred approach.
Letting Columns Default or Stay NULL
If a column has a DEFAULT value defined in the schema and you don’t mention it in your INSERT, SQLite fills it in automatically:
INSERT INTO books (title, author, genre, price) VALUES
('Kafka on the Shore', 'Haruki Murakami', 'Magical Realism', 15.00);
Since in_stock wasn’t provided, it falls back to the DEFAULT value of 1, defined back when we created the table. If a column has no default and you don’t provide a value, and it’s nullable, it simply becomes NULL.
Inserting Data from Another Query
This is one of the more powerful and underused features of INSERT. Instead of hardcoding values, you can insert the result of a SELECT statement directly into a table.
CREATE TABLE fantasy_books (
id INTEGER PRIMARY KEY,
title TEXT,
author TEXT,
price REAL
);
INSERT INTO fantasy_books (title, author, price)
SELECT title, author, price
FROM books
WHERE genre = 'Fantasy';
This pulls every fantasy book out of the books table and copies it straight into fantasy_books, no manual typing required. This pattern is enormously useful when you’re archiving old records, building summary tables, or migrating data between schemas.
Handling Conflicts: INSERT OR IGNORE, INSERT OR REPLACE
Real-world data isn’t always clean, and sooner or later you’ll try to insert a row that violates a constraint — usually a UNIQUE or PRIMARY KEY constraint. SQLite gives you a few ways to handle that gracefully instead of letting the whole operation fail with an error.
Let’s add a UNIQUE constraint to demonstrate:
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
country TEXT
);
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
If you try to insert ‘Haruki Murakami’ again, SQLite throws a constraint violation error. But if you use INSERT OR IGNORE, SQLite just quietly skips the conflicting row instead of raising an error:
INSERT OR IGNORE INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
No error, no duplicate — the statement just does nothing for that row. This is great for idempotent scripts, like a seed script you might run multiple times during development without wanting duplicate errors every time.
INSERT OR REPLACE works differently — instead of skipping the conflicting row, it deletes the old one and inserts the new one in its place:
INSERT OR REPLACE INTO authors (id, name, country) VALUES (1, 'Haruki Murakami', 'Japan (Kyoto)');
Be careful with this one. Because it’s technically a delete-then-insert operation under the hood, if other tables have foreign keys referencing that row, and you don’t have ON DELETE CASCADE or similar set up thoughtfully, you can run into unexpected side effects. I’ve been bitten by this before on a project where cascading deletes wiped out related records I didn’t intend to touch.
There’s also a modern, more precise alternative introduced in SQLite: the ON CONFLICT clause, often called an “upsert.”
INSERT INTO authors (name, country)
VALUES ('Haruki Murakami', 'Japan')
ON CONFLICT(name) DO UPDATE SET country = excluded.country;
This says: try to insert; if a row with a conflicting name already exists, update its country column instead. The excluded keyword refers to the row that would have been inserted. I much prefer this approach over INSERT OR REPLACE in real applications because it’s explicit about exactly which columns get updated, rather than replacing the entire row.
Using Transactions for Bulk Inserts
If you’re inserting a large number of rows — say, loading a CSV file with tens of thousands of records — wrapping your inserts in a transaction makes an enormous difference in performance. By default, SQLite treats every INSERT statement as its own transaction, which means it writes to disk and confirms that write after every single statement. That’s safe, but slow when you’re doing it thousands of times in a row.
BEGIN TRANSACTION;
INSERT INTO books (title, author, genre, price) VALUES ('Book One', 'Author A', 'Genre A', 9.99);
INSERT INTO books (title, author, genre, price) VALUES ('Book Two', 'Author B', 'Genre B', 11.99);
INSERT INTO books (title, author, genre, price) VALUES ('Book Three', 'Author C', 'Genre C', 8.49);
COMMIT;
Wrapping many inserts inside a single BEGIN/COMMIT block can turn a process that takes minutes into one that takes seconds, because SQLite only has to finalize the write to disk once, at the end, instead of after every individual statement.
Data Type Considerations When Inserting
SQLite uses what’s called dynamic typing with type affinity. In practice, this means SQLite is fairly forgiving about the type of value you insert into a column, even if that type doesn’t strictly match the column’s declared type. If you insert the text '12.99' into a column declared as REAL, SQLite will typically convert it. That flexibility is convenient, but it can also hide bugs — inserting a string into a column that’s supposed to hold numbers might succeed silently when you expected an error.
To catch these problems early, consider using CHECK constraints:
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL CHECK (price >= 0)
);
INSERT INTO products (name, price) VALUES ('Notebook', -5.00);
That last INSERT will fail because the CHECK constraint rejects negative prices. This kind of validation at the database level is a safety net that catches mistakes your application code might otherwise let slip through.
Common Mistakes to Avoid
Forgetting quotes around text values. INSERT INTO books (title) VALUES (The Hobbit) fails because SQLite interprets The and Hobbit as unquoted identifiers, not a string. Always wrap text in single quotes: 'The Hobbit'.
Mismatched column and value counts. If you list four columns but only provide three values, SQLite throws an error immediately. Always double check the count matches, especially in generated or templated SQL.
Not handling NOT NULL columns. If a column is defined as NOT NULL with no default, and you don’t provide a value for it, the insert fails. Know your schema’s constraints before writing bulk insert scripts.
Running thousands of individual INSERT statements outside a transaction. As mentioned above, this is a major and very common performance mistake, especially when importing data from files.
Assuming INSERT OR REPLACE is a safe update mechanism. It’s a delete-and-insert operation in disguise, and it resets any columns you didn’t explicitly provide back to their defaults, plus it can trigger unwanted cascading behavior on related tables. Prefer ON CONFLICT ... DO UPDATE when you want true update-like behavior.
Best Practices Worth Adopting
Always specify your column list explicitly, even when you’re providing all of them. It protects your script against future schema changes.
Batch your inserts into multi-row VALUES statements or wrap sequential inserts in transactions whenever you’re loading more than a handful of rows.
Use INSERT OR IGNORE or ON CONFLICT DO NOTHING for scripts that need to be safely re-runnable, like development seed data.
Prefer ON CONFLICT ... DO UPDATE over INSERT OR REPLACE when you need upsert behavior, since it gives you precise control over which columns actually change.
Add CHECK constraints and NOT NULL where appropriate at the schema level, so bad data gets rejected at the point of insertion rather than causing problems later when you’re querying it.
Test your insert statements against a copy of your schema before running them against production data, especially anything involving OR REPLACE, which can have destructive side effects you didn’t intend.
Wrapping Up
INSERT might look like the simplest command in SQL, and for basic use cases, it is. But once you start dealing with real applications — conflict handling, bulk loading, data migrations between tables — there’s a surprising amount of nuance packed into this one keyword. Get comfortable with the basic syntax first, then work your way through conflict resolution and transactions, since those are the two areas where most real-world INSERT problems tend to show up. As always, the fastest way to really understand this is to open a SQLite database and start inserting rows yourself, deliberately breaking things so you understand exactly why they broke.
