Everything in the earlier modules read data. From here on the statements change it, and the first one is INSERT: it adds new rows to a table. This lesson covers the single-row and multi-row forms, the column list and why it matters, what happens to the columns you leave out (defaults, NULL and AUTO_INCREMENT), how to read back a generated id, and INSERT INTO ... SELECT, which inserts the result of a query. The examples use MySQL 8 with InnoDB tables; the differences that come up in PostgreSQL interviews are called out where they matter.

The basic form

INSERT INTO products (id, name, category, price, in_stock)
VALUES (101, 'Desk lamp', 'lighting', 24.99, 12);

The statement names the table, then a column list in parentheses, then VALUES with one value per listed column, in the same order. Strings and dates are quoted with single quotes ('2024-03-01'), numbers are not, and NULL is written bare. A single quote inside a string is doubled: 'O''Brien'.

You may leave out the column list:

INSERT INTO products VALUES (102, 'Floor lamp', 'lighting', 79.00, 4);

This only works when you supply a value for every column in the order the table declares them, and it is a habit to avoid. The day someone adds a column, the statement either fails ("Column count doesn't match value count") or quietly puts values into the wrong columns if the types happen to fit. Always write the column list; it doubles as documentation.

Many rows in one statement

INSERT INTO products (id, name, category, price, in_stock)
VALUES
  (103, 'Bulb 9W',   'lighting', 3.50, 200),
  (104, 'Bulb 12W',  'lighting', 4.25, 150),
  (105, 'Extension', 'power',   15.00,  30);

Several value lists separated by commas insert several rows. It is faster than one statement per row (one parse, one round trip, one log flush under autocommit) and it is atomic: if row three violates a constraint, none of the three are inserted. Bulk loads of thousands of rows are usually batched this way, a few hundred or a thousand rows per statement.

The columns you leave out

A column missing from the column list gets, in order of preference:

1. its DEFAULT value, if the table declares one;
2. the next number, if it is an AUTO_INCREMENT column;
3. NULL, if the column allows it;
4. otherwise an error in MySQL's default strict mode ("Field 'x' doesn't have a default value").

CREATE TABLE tickets (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  subject  VARCHAR(100) NOT NULL,
  status   VARCHAR(10)  NOT NULL DEFAULT 'open',
  assignee VARCHAR(40)
);

INSERT INTO tickets (subject) VALUES ('Printer jams'), ('VPN drops');
SELECT id, subject, status, assignee FROM tickets ORDER BY id;

The id came from AUTO_INCREMENT, status from its default, assignee is NULL. Writing NULL explicitly for a column with a default does not give you the default — you asked for NULL, and a NOT NULL column refuses it. To ask for the default by name, MySQL accepts the keyword DEFAULT in a value list (VALUES (DEFAULT, 'x')); leaving the column out of the list is the portable way.

Generated ids and LAST_INSERT_ID

When the database numbers the rows, the application usually needs the number it just got — to insert the order's lines, say. In MySQL:

INSERT INTO orders (customer_id, placed_on) VALUES (42, '2024-05-01');
SELECT LAST_INSERT_ID();          -- the id generated for that row

LAST_INSERT_ID() is per connection, so two sessions inserting at once each see their own value — never compute "the next id" with SELECT MAX(id) + 1, which two sessions can read at the same moment. After a multi-row insert it returns the id of the first row. In PostgreSQL the idiom is INSERT ... RETURNING id, which returns the generated columns as a result set; MySQL has no RETURNING.

Two facts about InnoDB's counter that interviewers like: ids are not guaranteed to be gap-free (a rolled-back insert still consumes its number), and a deleted id is never handed out again — the counter only moves forward. Treat surrogate ids as labels, not as a count of rows.

Copying rows with INSERT ... SELECT

Instead of literal values, an INSERT can take the rows of a query:

INSERT INTO orders_archive (id, customer, total, placed_on)
SELECT id, customer, total, placed_on
FROM orders
WHERE placed_on < '2023-01-01';

The SELECT can be anything — joins, GROUP BY, DISTINCT — as long as it returns one column per listed column. The match is by position, not by name: the SELECT's third column goes into the INSERT's third column whatever either is called, so keep the two lists in the same order. This is how you archive, fill a summary table, or move rows from a staging table after an import:

INSERT INTO daily_sales (sale_date, orders, revenue)
SELECT placed_on, COUNT(*), SUM(total)
FROM orders
GROUP BY placed_on;

A close cousin creates the table and fills it in one go: CREATE TABLE top_customers AS SELECT .... It copies column names and types from the query but not keys, indexes or defaults, so it suits scratch and reporting tables rather than real schema (the schema-design module returns to it).

When an INSERT fails

An insert that breaks a rule is refused and changes nothing:

• a duplicate value in a PRIMARY KEY or UNIQUE column — "Duplicate entry '7' for key 'PRIMARY'";
• NULL (or a missing value with no default) in a NOT NULL column;
• a value that fails a CHECK constraint (MySQL 8.0.16 and later);
• in strict mode, a string too long for its VARCHAR, or a value of the wrong type.

What to do with duplicates on purpose — skip them, overwrite them or update them — is the subject of the upserts lesson. One environment note: the practice engine here is MySQL syntax on SQLite, which does not truncate or reject an over-long string the way strict MySQL does, so the exercises never rely on that.

Pitfalls

• An INSERT without a column list, which breaks when the table changes.
• Expecting NULL to mean "use the default".
• Computing ids with MAX(id) + 1 instead of AUTO_INCREMENT.
• An INSERT ... SELECT whose two column lists are in different orders — the types often match, so nothing complains.
• Thousands of single-row inserts under autocommit, each a separate transaction and log flush; batch them.

Key takeaways

• INSERT INTO t (cols) VALUES (...), (...); — always the column list; many rows per statement.
• Omitted columns take their DEFAULT, an AUTO_INCREMENT number or NULL; explicit NULL is not the default.
• LAST_INSERT_ID() (MySQL) or RETURNING (PostgreSQL) for the generated id; never MAX(id) + 1.
• INSERT INTO ... SELECT copies query results, matched by position.
• A constraint violation rejects the whole statement.