Lesson 19 of 26 · see the practice database

Adding rows with INSERT

The first eighteen lessons only read data. Now you will change it. There are three statements for that: INSERT adds rows, UPDATE changes them and DELETE removes them. This lesson is about the first.

The practice box keeps its own copy of the database in your browser, so you can change anything. Reset the database puts everything back. The examples in this lesson build on one another, so run them in order.

Adding one row

Name the table, list the columns, and give one value for each column in the same order.

INSERT INTO customers (id, name, city, state, joined, email)
VALUES (13, 'Lata Bose', 'Kolkata', 'West Bengal', '2026-11-01', 'lata@example.com');

An INSERT returns no rows. The database only reports that it is done. Read the table to see the new row.

SELECT * FROM customers WHERE id = 13;

The rules for values are the ones you know: text and dates in single quotation marks, numbers without.

Always list the columns

You may leave the column list out, and then you must supply a value for every column in the order the table defines them.

INSERT INTO customers
VALUES (14, 'Omar Sheikh', 'Bhopal', 'Madhya Pradesh', '2026-11-02', NULL);

It is shorter and it is fragile. If a column is ever added to the table or the order changes, the statement breaks, or worse, puts values in the wrong columns. Name the columns in anything you save.

Leaving columns out

List only the columns you have values for. A column you leave out gets its default value, which is NULL unless the table says otherwise.

INSERT INTO customers (id, name, city, state, joined)
VALUES (15, 'Geeta Pillai', 'Kochi', 'Kerala', '2026-11-03');

SELECT id, name, email FROM customers WHERE id >= 13;

The email of the last customer is NULL, because it was not given.

Letting the database number the row

In most tables, the id is filled in by the database. In SQLite, a column declared INTEGER PRIMARY KEY gets the next number if you leave it out.

INSERT INTO products (name, category, price, stock)
VALUES ('Desk Lamp', 'Home', 599, 15);

SELECT id, name FROM products ORDER BY id DESC LIMIT 2;

The other databases have the same idea under different names: AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, and GENERATED ... AS IDENTITY or SERIAL in PostgreSQL. In all of them you leave the column out of the INSERT and the number arrives by itself.

Several rows at once

Give several sets of values, separated by commas.

INSERT INTO products (name, category, price, stock) VALUES
  ('Eraser pack of 5', 'Stationery', 25, 400),
  ('Glue Stick', 'Stationery', 35, 120),
  ('Table Fan', 'Electronics', 1850, 8);

One statement with many rows is quicker than many statements with one row each.

When an INSERT is refused

A table can carry rules, called constraints, and the database rejects a row that breaks one.

A value missing where one is required:

INSERT INTO customers (id, name) VALUES (16, 'Nobody');

Error: NOT NULL constraint failed: customers.city

The city column was declared NOT NULL, so a row without a city is not accepted.

A primary key that already exists:

INSERT INTO customers (id, name, city, state, joined)
VALUES (1, 'Second Asha', 'Delhi', 'Delhi', '2026-11-04');

Error: UNIQUE constraint failed: customers.id

There is already a customer number 1, and a primary key must be unique.

These refusals are the database protecting your data. A table that accepts anything fills up with rubbish. The lesson on CREATE TABLE shows how the rules are set.

Quotation marks inside text

To put a single quotation mark inside a text value, write it twice.

INSERT INTO customers (id, name, city, state, joined)
VALUES (16, 'Mary D''Souza', 'Panaji', 'Goa', '2026-11-05');

SELECT name FROM customers WHERE id = 16;

Copying rows from a query

In place of VALUES, an INSERT can take a SELECT. Every row the query returns is added. To try it, first make an empty table to copy into. CREATE TABLE is explained two lessons from now.

CREATE TABLE punjab_customers (id INTEGER, name TEXT, city TEXT);

INSERT INTO punjab_customers (id, name, city)
SELECT id, name, city FROM customers WHERE state = 'Punjab';

SELECT * FROM punjab_customers;

This is how data is moved between tables, archived, or loaded from a staging table. The SELECT can be as elaborate as you like, with joins and groups, as long as its columns line up with the column list.

A new order from start to finish

Adding one order to this shop takes several inserts, because an order is spread over two tables. First the order itself:

INSERT INTO orders (id, customer_id, order_date, status)
VALUES (21, 13, '2026-11-06', 'pending');

Then its lines:

INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (21, 5, 3),
  (21, 13, 1);

And a check, joining the new rows:

SELECT c.name, p.name AS product, oi.quantity
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
WHERE o.id = 21;

Statements that belong together like this should succeed or fail as a group. Databases provide transactions for that: you write BEGIN, run the statements, and then COMMIT to keep them all or ROLLBACK to undo them all. Programs that take real orders always wrap the inserts in a transaction.

Good habits

• List the columns.
• After an insert, read the row back with a SELECT.
• Let the database give out the id numbers.
• Type dates as year-month-day.
• When loading many rows, use one multi-row statement.

Try it yourself

In the practice box, these change your copy of the database. Press Reset the database when you want the original back.

INSERT INTO products (name, category, price, stock) VALUES ('Pencil Box', 'Stationery', 85, 60);

INSERT INTO employees (id, name, department, salary, manager_id, hired) VALUES (9, 'Sara Paul', 'Support', 31000, 7, '2026-11-10');

INSERT INTO customers (id, name, city, state, joined) VALUES (20, 'Dev Arora', 'Jaipur', 'Rajasthan', '2026-11-11'), (21, 'Ila Mathur', 'Jaipur', 'Rajasthan', '2026-11-11');

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.