INSERT: new rows

In short: INSERT INTO adds new rows to a table, one or many at once or from the result of a SELECT, and RETURNING gives back columns of the new row, such as its id.

Problem

A new customer

So far you have only read from the database. But new things go into it every day. Today Vesela Raykova from Pleven signed up on the website.

The website has to write a new row into customers. Nobody opens the table by hand: the program sends a query to the database.

The idea

INSERT INTO

INSERT INTO says which table and which columns, and VALUES gives the values in the same order:

SQLdatabase: shop

INSERT INTO customers (name, email, city, joined_at)
VALUES ('Vesela Raykova', 'vesela.r@example.com', 'Pleven', '2026-10-25');

SELECT * FROM customers WHERE joined_at >= '2026-09-01';

INSERT INTO customers (name, email, city, joined_at) VALUES ('Vesela Raykova', 'vesela.r@example.com', 'Pleven', '2026-10-25'); SELECT * FROM customers WHERE joined_at >= '2026-09-01';

▶ RunRestore the queryFresh database

The column id is not in the list. It is a primary key of the kind INTEGER PRIMARY KEY, so SQLite itself gives it a number one higher than the largest: 21. A column that is not in the list gets its default value (for stock in the shop it is 0), and if there is none, NULL.

A field can hold several statements. They run one after another, and the SELECT at the end shows what happened.

Each field has its own database and keeps it between runs. Run the query again: the second time the database refuses, because Vesela is already in with that email. The “Fresh database” button puts the database back as it was at the start.

See it

Without the list of columns

The list of columns can be left out. Then the values must cover all the columns, in the order they have in the table:

SQLdatabase: shop

INSERT INTO customers
VALUES (21, 'Vesela Raykova', 'vesela.r@example.com', 'Pleven', '2026-10-25');

SELECT * FROM customers WHERE id = 21;

INSERT INTO customers VALUES (21, 'Vesela Raykova', 'vesela.r@example.com', 'Pleven', '2026-10-25'); SELECT * FROM customers WHERE id = 21;

▶ RunRestore the queryFresh database

Leave one out, and the database refuses:

SQLdatabase: shop

INSERT INTO customers
VALUES ('Vesela Raykova', 'vesela.r@example.com', 'Pleven', '2026-10-25');

INSERT INTO customers VALUES ('Vesela Raykova', 'vesela.r@example.com', 'Pleven', '2026-10-25');

▶ RunRestore the queryFresh database

Here id is missing. Without a list, the database cannot tell which value is for which column. So always list the columns. That way you can see what goes where, and the query does not break when the table gets a new column.

Try it

A new product

The shop starts selling ayran. Add the product “Ayran” in category 4 (Dairy), at 1.30 €, with 30 in stock, added today (2026-10-25).

SQLdatabase: shop

SELECT id, name, price, stock FROM products WHERE category_id = 4;

SELECT id, name, price, stock FROM products WHERE category_id = 4;

Check▶ Run

The idea

RETURNING

Yavor Penev places his first order. The website saves the order and then has to save the products in it. For that it needs the number of the new order, and the database hands that out.

RETURNING at the end of an INSERT gives back columns of the row that was just saved:

SQLdatabase: shop

INSERT INTO orders (customer_id, ordered_at, status)
VALUES (20, '2026-10-25', 'new')
RETURNING id;

INSERT INTO orders (customer_id, ordered_at, status) VALUES (20, '2026-10-25', 'new') RETURNING id;

▶ RunRestore the queryFresh database

The order is number 61. Without RETURNING you would need a second query to find out. RETURNING takes any columns: RETURNING id, ordered_at or RETURNING *.

More precisely, if you are curious

RETURNING exists in SQLite (since version 3.35) and in PostgreSQL. In SQL Server the same thing is written with OUTPUT. MySQL does not have it: there the number is read with LAST_INSERT_ID() after the insert. SQLite has a similar function, last_insert_rowid().

The idea

Many rows at once

Yavor's order has coffee and two cartons of fresh milk. Several rows go in with one INSERT: after VALUES come several sets of brackets, separated by commas. In this database order 61 already exists:

SQLdatabase: shop

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (61, 17, 1, 14.9),
       (61, 5, 2, 1.6);

SELECT * FROM order_items WHERE order_id = 61;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 17, 1, 14.9), (61, 5, 2, 1.6); SELECT * FROM order_items WHERE order_id = 61;

▶ RunRestore the queryFresh database

The rows can also come from a query. Maria clicks “Order again” on her last order (60). The new order is 61, and the products come from a SELECT instead of VALUES:

SQLdatabase: shop

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 61, oi.product_id, oi.quantity, p.price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 60;

SELECT * FROM order_items WHERE order_id = 61;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) SELECT 61, oi.product_id, oi.quantity, p.price FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.order_id = 60; SELECT * FROM order_items WHERE order_id = 61;

▶ RunRestore the queryFresh database

This is INSERT … SELECT: whatever the SELECT returns is saved. The columns of the SELECT must match the list after INSERT INTO. The price is taken from products, not from the old order, because it may have changed since then.

See it

The database says no

The shop's tables have rules. Break one, and the database refuses the row and says which rule was broken.

A customer's email must be unique (UNIQUE):

SQLdatabase: shop

INSERT INTO customers (name, email, joined_at)
VALUES ('Maria Ivanova', 'maria.ivanova@example.com', '2026-10-25');

INSERT INTO customers (name, email, joined_at) VALUES ('Maria Ivanova', 'maria.ivanova@example.com', '2026-10-25');

▶ RunRestore the queryFresh database

The name cannot be missing (NOT NULL):

SQLdatabase: shop

INSERT INTO customers (email, joined_at)
VALUES ('new.client@example.com', '2026-10-25');

INSERT INTO customers (email, joined_at) VALUES ('new.client@example.com', '2026-10-25');

▶ RunRestore the queryFresh database

The price cannot be negative (CHECK):

SQLdatabase: shop

INSERT INTO products (name, category_id, price, stock, added_at)
VALUES ('Ayran', 4, -1.3, 30, '2026-10-25');

INSERT INTO products (name, category_id, price, stock, added_at) VALUES ('Ayran', 4, -1.3, 30, '2026-10-25');

▶ RunRestore the queryFresh database

An order must belong to a customer who exists. customer_id is a foreign key to customers, and there is no customer 99:

SQLdatabase: shop

INSERT INTO orders (customer_id, ordered_at, status)
VALUES (99, '2026-10-25', 'new');

INSERT INTO orders (customer_id, ordered_at, status) VALUES (99, '2026-10-25', 'new');

▶ RunRestore the queryFresh database

Each time the row is not saved and nothing in the database changes. That is a good thing: a bug in the program cannot fill the database with orders that belong to nobody.

These rules are written into the table itself when it is created. In the next module you will write them yourself.

Try it

A review without text

Simona Yordanova (customer 17) gives fresh milk (product 5) 5 stars today, 2026-10-25, without writing any text. Save the review.

SQLdatabase: shop

SELECT * FROM reviews WHERE product_id = 5;

SELECT * FROM reviews WHERE product_id = 5;

Check▶ Run

Exercise

Two tasks

1. The shop is expanding its “Home” department (category 2). Add two subcategories with one query: “Garden” and “Bathroom”.

The two new categories in “Home”:

SQLdatabase: shop

SELECT * FROM categories;

SELECT * FROM categories;

Check▶ Run

2. Ognyan Slavov from Varna signs up today with the email ognyan.s@example.com. The website wants to show him his profile straight away, with the number the database gave him. Save him and return the whole new row with the same query.

The new customer, the whole row:

SQLdatabase: shop

SELECT id, name, joined_at FROM customers WHERE city = 'Varna';

SELECT id, name, joined_at FROM customers WHERE city = 'Varna';

Check▶ Run

Test

Check yourself

1. The column stock is missing from INSERT INTO products. What will it hold?

The value from the previous rowAlways an errorThe default value of the column (0 in the shop), or NULL if there is none

2. Why is it better to list the columns in an INSERT?

INSERT does not work without a listThe query runs fasterThe values go into the right columns, and the query does not break when the table gets a new column

3. What does RETURNING id at the end of an INSERT do?

It shows every id in the tableIt gives back the number the database gave to the new rowIt cancels the insert if the id exists

4. You INSERT an order with customer_id = 99, and there is no customer 99. What happens?

A FOREIGN KEY error, and the order is not savedThe order is saved without a customerThe database creates customer 99

Check

Start the lesson over

Related lessons

• Builds on: WHERE: filtering rows · NULL: no value · Primary key and foreign key · JOIN: two tables in one query
• Continues in: UPDATE: changing rows · DELETE: deleting rows · UPSERT: insert or update · The database: from creating it to DROP
• Tasks on this topic: Changing data