Learn SQL: Adding rows with INSERT

New in the shop window

"Tomorrow there are apple turnovers on the counter, and they need to be online too." Jens brushes the flour off his hands. Until now you only fetched data. Now you add some yourself.

One more row

INSERT INTO adds a row. First you name the table and, between brackets, the columns you fill in. After VALUES come the values, in the same order.

You don't have to fill in the id: an INTEGER PRIMARY KEY gets the next number by itself. Below you see the table after the INSERT, with the new row in green.

You can't break anything here: after every query the database is back the way it was.

INSERT INTO products (name, category, price, stock) VALUES ('Apple turnover', 'cake', 2.6, 12)

Required, or with a default

Not every column may be skipped. When the table was made, every column got rules. A column with NOT NULL is required. A column with a DEFAULT gets a default value if you leave it out.

Below you see the rules of products. Leave out stock and it becomes 0. Leave out name and SQLite refuses the row.

SELECT name AS column_name, type, CASE "notnull" WHEN 1 THEN 'yes' ELSE 'no' END AS required, dflt_value AS default_value FROM pragma_table_info('products')

Result
column_name	type	required	default_value
id	INTEGER	no	NULL
name	TEXT	yes	NULL
category	TEXT	yes	NULL
price	REAL	yes	NULL
stock	INTEGER	yes	0
gluten_free	INTEGER	yes	0
active	INTEGER	yes	1

Several rows at once

You can add several rows in one INSERT. Put them one after the other after VALUES, separated by commas.

Columns you don't name get their default value or stay NULL. Here the two new customers get no email and 0 points.

INSERT INTO customers (name, town) VALUES
  ('Hanne Smets', 'Leuven'),
  ('Ruben Vos', 'Herent')

Try it yourself

Put the Christmas bread in the web shop, category seasonal, at €9.50, with 8 in stock.

Ready for more?

The full lesson has 7 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson