INSERT

Adding rows with INSERT

Module 9 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Add new books to the table.

INSERT INTO the table, list the columns, then give the VALUES in the same order.

Next: INSERT INTO

On your own New data, same idea

The library has bought a new book. Add Matilda by Roald Dahl (Children, 1988, 240 pages) as book_id 9.

The database

8 rows

book_id	title	author	genre	year	pages
1	The Shining	Stephen King	Horror	1977	447
2	The Two Towers	J.R.R. Tolkien	Fantasy	1954	352
3	Charlie and the Chocolate Factory	Roald Dahl	Children	1964	155
4	The Girl with the Dragon Tattoo	Stieg Larsson	Thriller	2005	465
5	The Lion, the Witch and the Wardrobe	C.S. Lewis	Fantasy	1950	172
6	The Alchemist	Paulo Coelho	Fiction	1988	197
7	Angels & Demons	Dan Brown	Thriller	2000	616
8	And Then There Were None	Agatha Christie	Mystery	1939	264

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

books after your query Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How INSERT works

INSERT adds a new row at the end. Nothing else changes.

INSERT INTO books (book_id, title, genre, year)
VALUES (5, 'Matilda', 'Children', 1988);

books before

book_id	title	genre	year
1	Frankenstein	Horror	1818
2	Emma	Romance	1815
3	Dune	Science Fiction	1965
4	Carrie	Horror	1974

books after (1 row added)

book_id	title	genre	year
1	Frankenstein	Horror	1818
2	Emma	Romance	1815
3	Dune	Science Fiction	1965
4	Carrie	Horror	1974
5	Matilda	Children	1988

How do I add a row?

INSERT INTO books (title, author) VALUES ('Dune', 'Frank Herbert');
List the columns, then one value for each, in the same order.
Do I have to list the columns?

No, but then you must give a value for every column, in the table's order. Listing them is safer: it still works if someone adds a column later.
What about columns I leave out?

They get NULL (or a default the table sets). Here, leaving out book_id lets SQLite pick the next free number.
What if the text has an apostrophe?

Write it twice: 'Charlotte''s Web'. Two single quotes inside the text stand for one.
How is this like Excel?

Like typing a new row at the bottom of a table. Here nothing is saved: every run starts again from the original table.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Several rows in one INSERT: VALUES (...), (...)
Database	How	Note
SQLite (these lessons)	`Yes`	Since SQLite 3.7.11 (2012).
PostgreSQL	`Yes`
MySQL / MariaDB	`Yes`
SQL Server	`Yes`	Since SQL Server 2008, up to 1,000 rows at a time.
Oracle	`Since 23ai`	Before that: one INSERT per row, or INSERT ALL.
Sybase ASE	`No`	One INSERT per row.

Letting the database pick the next id
Database	How	Note
SQLite (these lessons)	`book_id INTEGER PRIMARY KEY`	Leave book_id out of the INSERT and SQLite uses the next number.
PostgreSQL	`book_id INT GENERATED ALWAYS AS IDENTITY`	Older code uses SERIAL.
MySQL / MariaDB	`book_id INT AUTO_INCREMENT PRIMARY KEY`
SQL Server	`book_id INT IDENTITY(1,1) PRIMARY KEY`
Oracle	`book_id NUMBER GENERATED AS IDENTITY`	Since Oracle 12c. Before that, a sequence.
Sybase ASE	`book_id NUMERIC(10,0) IDENTITY`

Know someone fighting with a spreadsheet? Send them this page.