How does an INSERT work, and what are the different ways to specify the rows it adds?
An insert adds one or more rows to a table. You name the table, you name the columns, and you give the values.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(200) NOT NULL,
created_on TEXT NOT NULL DEFAULT '2026-01-01'
);
-- One row.
INSERT INTO customers (id, name, email) VALUES (1, 'Ada Lovelace', '[email protected]');
-- Several rows in one statement.
INSERT INTO customers (id, name, email) VALUES
(2, 'Grace Hopper', '[email protected]'),
(3, 'Alan Turing', '[email protected]');
So what are the ways of specifying the rows? Four.
The first one is the values that you write out, as in the example above. The second one is to take them from a query with the words INSERT INTO ... SELECT. The third one is to let the database make some of them, with the word DEFAULT in place of a value. The fourth one is the upsert, where the same statement either adds a row or changes one that is already there.
-- The rows come from a query.
INSERT INTO customers (id, name, email)
SELECT id, name, email FROM new_signups;
-- The database makes the date.
INSERT INTO customers (id, name, email, created_on)
VALUES (4, 'Katherine Johnson', '[email protected]', DEFAULT);
The next question in this topic looks at the column list, which is the part that people leave out and then regret.