Adding rows

How do you add a row to a table?

Everything so far has read data. INSERT writes it. The basic form names the table, lists the columns you are filling, and gives the values in the same order: INSERT INTO books (title, author) VALUES ('Owls of the Valley', 'Nia Ford');.

Text values go in single quotes, numbers do not. If the text contains an apostrophe, double it: 'Wren''s Nest' stores Wren's Nest. Double quotes are for names of tables and columns, never for text values, even though SQLite sometimes lets that slide.

An INSERT returns no rows, so the Result view shows a summary of what changed instead of a table. Add a SELECT after it to see the table, or open the Changes view, which shows the new row in green. Every run starts from a fresh copy of the database, so you can try things as often as you like.

INSERT INTO books (title, author)
VALUES ('Owls of the Valley', 'Nia Ford');

SELECT id, title, author, copies, shelf
FROM books;

Why should you name the columns?

You can leave the column list out and give a value for every column in table order: INSERT INTO books VALUES (7, 'Owls of the Valley', 'Nia Ford', 1, 'Nature', '2026-10-01');. It works today. It breaks the day someone adds a column or reorders them, and until then nobody reading it can tell which value is which without opening the schema.

Naming the columns makes the statement say what it means and lets you skip any column with a default. It also catches mistakes early: if you list three columns and give two values, SQLite stops with an error instead of shifting everything along.

The column order in your list does not have to match the table. (author, title) with ('Nia Ford', 'Owls of the Valley') is fine. Each value must sit in the same position as its column.

How do you add several rows at once?

Put several bracketed rows after VALUES, separated by commas. One statement, one trip to the database, and either all the rows go in or, if one fails a rule, none of them do.

Lay it out one row per line, as in the code below. It is far easier to spot a missing quote or a swapped value when the rows line up. AI tools often generate long single-line inserts; reformatting them like this is the quickest way to check them.

INSERT INTO books (title, author, copies, shelf) VALUES
  ('Owls of the Valley', 'Nia Ford', 2, 'Nature'),
  ('The Quiet Hedge', 'Paul Kerr', 1, 'Nature'),
  ('Rye and Wild Yeast', 'Jo Ashby', 1, 'Cookery');

SELECT * FROM books;

What happens to the columns you leave out?

Each column you skip gets its default. The schema for books gives copies a default of 1, shelf a default of 'New arrivals', and added_on a default of CURRENT_DATE, which is today's date as YYYY-MM-DD text. A column with no default gets NULL.

If that column is also NOT NULL, there is no valid value left and the insert fails with NOT NULL constraint failed: books.title. That is a useful error: it tells you a required piece of data is missing before a half-empty row gets in.

You can ask for a default explicitly as well. Leaving the column out of the list is the usual way. In a full insert, PostgreSQL and MySQL also accept the keyword DEFAULT in place of a value; SQLite does not, so leave the column out there.

Where do the ids come from?

Leave the id out and SQLite fills it in. For a column declared INTEGER PRIMARY KEY, the new id is normally one more than the largest id in the table, so the donations become 7 and 8. After an insert, SELECT last_insert_rowid(); tells you the id of the last row added on this connection, which you need when you go on to insert rows that point at it.

Do not type ids in yourself unless you are loading data that already has them. Two people choosing "the next number" at the same moment will clash, and the database is better at it. If you add AUTOINCREMENT to the column, SQLite also promises never to reuse an id from a deleted row; without it, an id freed by deleting the last row can be handed out again.

Other databases use other words for the same idea: GENERATED ALWAYS AS IDENTITY or SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL. PostgreSQL also offers INSERT ... RETURNING id, which SQLite has supported since 3.35 and which the next lesson uses.

INSERT INTO books (title, author)
VALUES ('The Quiet Hedge', 'Paul Kerr');

SELECT last_insert_rowid() AS new_id;

How do you copy rows from another table?

Replace VALUES with a SELECT. Every row the SELECT returns becomes a new row. This is how you promote approved requests into the catalogue, archive old loans, or load a cleaned-up copy of some data.

The SELECT must return exactly as many columns as you listed, in the same order. Column names are ignored here, only positions count, so SELECT author, title into (title, author) swaps them silently. You can also put fixed values in the SELECT, such as 2 for copies or 'Nature' for shelf.

Run the SELECT on its own first. If it returns the rows you expect, with the columns in the right order, put the INSERT INTO ... line on top. That habit saves a lot of cleaning up.

INSERT INTO books (title, author, shelf)
SELECT title, author, 'Requested'
FROM requests
WHERE approved = 1;

SELECT id, title, author, shelf FROM books;

How do you check what an insert did?

Open the Changes view after any insert. It shows each table your code touched, with the added rows in green and every value in its column. Read across one new row: is the title in title, the author in author, the defaults what you expected?

Then count. Write down how many rows you expected to add, and compare it with the table's count before and after. If you meant to add two and the count went up by four, a WHERE is missing from your INSERT ... SELECT. A quick SELECT COUNT(*) FROM books; at the end of your code turns that into a habit.

What errors will you meet?

Insert errors are good news: the database refused bad data. Here are the ones beginners hit most, with what they mean.

• NOT NULL constraint failed: books.author: a required column has no value and no default.
• UNIQUE constraint failed: books.id: you gave an id that already exists. Leave the id out.
• FOREIGN KEY constraint failed: the row points at a parent row that does not exist, such as a loan for book 99.
• 2 values for 3 columns or table books has 6 columns but 5 values were supplied: the counts do not match.
• near "s": syntax error: usually an apostrophe inside text that was not doubled.

Field notes

• Name the columns you are filling, then give the values in that same order.
• Several bracketed rows after VALUES go in with one statement, all together or not at all.
• Columns you leave out get their default, or NULL if they have none.
• Leave an INTEGER PRIMARY KEY id out and SQLite picks the next number for you.

Easy to get wrong

Without a column list, SQLite expects a value for all six columns in table order.

Double quotes mean names of columns. Text values belong in single quotes; other databases reject the double-quoted version.

Values match columns by position. The swap goes in without any error, and without WHERE every request is copied.

The apostrophe ends the text early. Write two single quotes to store one.

Questions people ask

Can I undo an INSERT?

Delete the rows you added, or wrap the work in a transaction and roll it back, which a later lesson covers. Here every run starts fresh anyway.

How many rows can one INSERT add?

Thousands in SQLite. For very large loads, batches of a few hundred rows inside a transaction are a sensible pattern.

What if the row might already exist?

SQLite has INSERT OR IGNORE and INSERT ... ON CONFLICT DO NOTHING (or DO UPDATE). They need a UNIQUE rule to detect the clash, covered under rules for your data.

Why does added_on show today's date?

Its default is CURRENT_DATE, worked out when the row is inserted, in UTC.