How Does INSERT Work in SQL?

An INSERT adds one or more rows to a table. There are three practical forms. You can supply literal values with a VALUES clause, you can copy rows from another table with an INSERT ... SELECT, and you can copy rows from a table in another database with a dialect-specific form such as PostgreSQL's INSERT ... SELECT FROM other_db.schema.table.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL,
  city VARCHAR(80),
  tier VARCHAR(20) NOT NULL DEFAULT 'standard'
);

-- Form 1: literal values.
INSERT INTO customers (id, name, email, city)
VALUES (1, 'Ada Lovelace', '[email protected]', 'London');

-- Form 2: literal values, leaving the rest to their defaults.
INSERT INTO customers (id, name, email)
VALUES (2, 'Grace Hopper', '[email protected]');

-- Form 3: rows produced by a query.
INSERT INTO customers (id, name, email, city)
SELECT 3, 'Alan Turing', '[email protected]', 'London';

SELECT id, name, email, city, tier FROM customers ORDER BY id;

Three rows come back. Rows 1 and 3 have a city, and all three have the tier 'standard' — rows 1 and 3 because the column was not listed and the default applied, and row 2 because the default applied to a column that was omitted entirely.

Two rules govern what happens when you leave a column out. If the column is NOT NULL with no default, the insert fails. If the column allows NULL, it is set to NULL. If it has a DEFAULT, the default is used — and a default expression is evaluated per row, so DEFAULT (datetime('now')) gives each inserted row its own timestamp rather than the time the statement started.

The other thing worth knowing is that the columns you do not list keep whatever value they had, which for a fresh row means the default or NULL. That is why an INSERT with an explicit column list is safer than one without: it makes the statement independent of the table's column order and of any column that might be added later.

Without a column list, the values are matched to the table's columns purely by position. INSERT INTO customers VALUES (1, 'Ada', '[email protected]', 'London') puts the first value in the first column, the second in the second, and so on. If the table's column order changes — because someone ran an ALTER TABLE, or because a migration created the columns in a different order — the same statement inserts different data into different columns, or fails because a NOT NULL column ends up with the wrong value.

That is a latent bug rather than an immediate one, which is what makes it dangerous. The script was written against a schema that existed at the time, it works when you test it, and it breaks months later when a migration runs on a Tuesday afternoon. With an explicit column list the statement keeps meaning the same thing forever, because it names the columns rather than the positions.

There is a second, subtler problem: a column list makes the statement safe against a column being added. If somebody adds a referred_by column with a NOT NULL constraint and no default, every INSERT that omits the column list will start failing, while the ones with a column list keep working — they simply leave the new column to its default.

Two more reasons to always name the columns. It makes the statement readable to the next person, who can see exactly what is being written. And it lets you insert a partial row without having to supply dummy values for columns you do not care about, which is what you want when a column has a sensible default.

The one thing an explicit column list does not do is protect you from a wrong value in a named column. For that you need constraints — a CHECK for a range, a UNIQUE for a duplicate, a FOREIGN KEY for a reference — because the database, not the statement, is what enforces the rule.

Name the columns and let the table's defaults handle the rest. This is the form to use in application code, where the caller knows a handful of fields.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(200) NOT NULL,
  city VARCHAR(80),
  tier VARCHAR(20) NOT NULL DEFAULT 'standard',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO customers (name, email)
VALUES ('Joan Clarke', '[email protected]');

SELECT id, name, email, city, tier, created_at IS NOT NULL AS has_timestamp
FROM customers;

One row comes back. The id was assigned by the primary key, the city is NULL because the column allows it and no default was given, the tier is 'standard', and the created_at column has a timestamp because its default expression was evaluated at insert time.

Two details worth noticing. The id was left out of the column list entirely, and SQLite assigned it — with an INTEGER PRIMARY KEY, that column is an alias for the row's internal identifier, so leaving it out is how you get an auto-assigned key. The same statement on PostgreSQL would need the column to be an identity column or have a sequence, and MySQL would need AUTO_INCREMENT; the portable spelling of an auto-assigned key is GENERATED ALWAYS AS IDENTITY, which SQLite has supported since version 3.31.

And the timestamp default is a per-row expression, not a constant. The parentheses around datetime('now') are required in SQLite for a function call in a DEFAULT clause. If you need the same timestamp for every row in a multi-row insert, compute it once in the application and pass it in as a parameter instead.

One caution for production code: an INSERT that leaves columns to their defaults can quietly store a placeholder value. If the application always passes the same constant — say, a status of 'pending' — write that constant explicitly in the INSERT rather than relying on the column default, so the code says what it means and a change to the default cannot alter the behaviour of code that was written before the change.