Inserting Data

Prerequisites: Creating tables

INSERT INTO adds new rows to a table.

One row

INSERT INTO products (name, category, price)
VALUES ('Wireless Mouse', 'Accessories', 25.00);

SELECT * FROM products WHERE name = 'Wireless Mouse';

+----+----------------+-------------+-------+
| id |      name      |  category   | price |
+----+----------------+-------------+-------+
|  7 | Wireless Mouse | Accessories |    25 |
+----+----------------+-------------+-------+

List the columns you're filling, then the values in the same order.
id was left out, so the database assigned the next number.

Several rows at once

INSERT INTO products (name, category, price) VALUES
  ('Desk Lamp', 'Lighting', 29.99),
  ('Cable Organizer', 'Accessories', 12.50);

SELECT id, name FROM products WHERE id > 6;

+----+-----------------+
| id |      name       |
+----+-----------------+
|  7 | Desk Lamp       |
|  8 | Cable Organizer |
+----+-----------------+

Inserting many rows in one statement is much faster than one statement per row.

Copying rows from a query

INSERT ... SELECT inserts the results of a query — handy for archiving or building summary tables:

CREATE TABLE cancelled_orders (id INTEGER, customer_id INTEGER, ordered_on DATE);

INSERT INTO cancelled_orders (id, customer_id, ordered_on)
SELECT id, customer_id, ordered_on FROM orders WHERE status = 'cancelled';

SELECT * FROM cancelled_orders;

+----+-------------+------------+
| id | customer_id | ordered_on |
+----+-------------+------------+
|  5 |           3 | 2025-03-18 |
+----+-------------+------------+

Getting the new ID

Applications often need the ID of the row they just inserted. Use RETURNING (SQLite 3.35+, PostgreSQL) — other databases have functions such as MySQL's LAST_INSERT_ID():

INSERT INTO customers (name, email, joined_on)
VALUES ('Katherine Johnson', 'katherine@example.com', '2025-05-01')
RETURNING id, name;

+----+-------------------+
| id |       name        |
+----+-------------------+
|  7 | Katherine Johnson |
+----+-------------------+

Common mistakes

Leaving out the column list: INSERT INTO products VALUES (...) relies on the table's column order and breaks when columns are added. Always list the columns.
Violating a constraint — for example, inserting a duplicate email into customers gives UNIQUE constraint failed.