INSERT

So far we've only read data. INSERT adds new rows to a table: a new customer signing up, an order being placed. You can insert one row, many rows at once, or the result of a query. Each editor starts from a fresh copy of the database, so experiment freely.

Inserting one row

INSERT INTO customers (id, name, email, city, country, signup_date)
VALUES (11, 'Kofi Mensah', 'kofi.mensah@example.com', 'Dublin', 'Ireland', '2026-03-10');

SELECT * FROM customers WHERE id = 11;

List the columns, then the values in the same order. Text and dates go in single quotes.

Always name the columns

-- Works, but fragile: depends on the table's exact column order
INSERT INTO departments VALUES (6, 'Legal', 'Lisbon');

Without a column list you must supply every column in the table's order. If someone later adds or reorders columns, the statement breaks or — worse — puts values in the wrong columns. Naming columns also lets you skip optional ones.

Leaving columns out

INSERT INTO products (name, category, price)
VALUES ('Bamboo Cutting Board', 'Home', 22.00);

SELECT * FROM products WHERE name LIKE 'Bamboo%';

Omitted columns get their default value: stock has DEFAULT 0, and id — an INTEGER PRIMARY KEY in SQLite — is generated automatically as the next number. Columns with no default become NULL, and leaving out a NOT NULL column without a default is an error:

INSERT INTO products (name, price) VALUES ('Mystery Item', 5.00);

Error: NOT NULL constraint failed: products.category

Auto-generated ids in other databases

Inserting several rows

INSERT INTO departments (name, location) VALUES
  ('Legal', 'Lisbon'),
  ('Finance', 'Dublin'),
  ('Design', 'Berlin');

SELECT * FROM departments WHERE id > 5;

A multi-row VALUES list is much faster than many single inserts. It's supported by all major databases (Oracle from version 23ai).

Inserting the result of a query

CREATE TABLE vip_customers (customer_id INTEGER PRIMARY KEY, total_spent DECIMAL(10, 2));

INSERT INTO vip_customers (customer_id, total_spent)
SELECT o.customer_id, round(SUM(oi.quantity * oi.unit_price), 2)
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY o.customer_id
HAVING SUM(oi.quantity * oi.unit_price) > 150;

SELECT * FROM vip_customers;

INSERT … SELECT copies data between tables without going through your application — for archiving, building summary tables or migrating data.

Constraint violations

INSERT INTO customers (id, name, email, country, signup_date)
VALUES (12, 'Ana Duplicate', 'ana.pereira@example.com', 'Portugal', '2026-03-10');

Error: UNIQUE constraint failed: customers.email

The database rejects rows that break the table's rules — a duplicate email in a UNIQUE column, a negative price that fails a CHECK, an order for a customer that doesn't exist. These rules (lessons 42–43) protect data quality no matter which application writes the data.

Getting the generated id back

After inserting, applications usually need the new row's id. Use RETURNING id (PostgreSQL, SQLite, MariaDB — lesson 38), OUTPUT inserted.id (SQL Server), or LAST_INSERT_ID() (MySQL).

Try it yourself

Place a new order for customer 10 (Julia) containing two products, then view it.