SQL INSERT

INSERT

INSERT adds new rows to a table. You name the columns you fill, and every column you leave out gets its default value.

Syntax

How an INSERT runs: rows come from VALUES or a SELECT → missing columns get their defaults → rules such as NOT NULL and UNIQUE are checked → ON CONFLICT handles duplicates → RETURNING shows the stored rows

If any row breaks a rule, the whole statement fails and no row is added.

ON CONFLICT and RETURNING are PostgreSQL's. MySQL handles duplicates with ON DUPLICATE KEY UPDATE, SQL Server and Oracle with MERGE, and SQL Server shows the stored rows with OUTPUT.

Code
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...)     -- or: SELECT ...
ON CONFLICT (column) DO NOTHING | DO UPDATE SET column = EXCLUDED.column
RETURNING column1, column2, ...;

Part	Meaning
`(column1, column2)`	the columns you fill, in the same order as the values
`VALUES (...), (...)`	one bracket per row
`DEFAULT`	use the column's default for this value
`SELECT ...`	add the rows a query returns, instead of `VALUES`
`ON CONFLICT (column)`	what to do when a row would repeat a unique value
`EXCLUDED.column`	the value you tried to insert
`RETURNING`	show the stored rows, like a `SELECT`

Examples

One row

Add a single record, filling only the columns you name.

Code
INSERT INTO categories (category_name, description)
VALUES ('Garden', 'Plants, pots and tools');
 
-- Text goes in single quotes, numbers don't
INSERT INTO products (product_name, category_id, price)
VALUES ('Steel Bottle 1L', 4, 549);

Defaults and RETURNING

See the values the database filled in for you.

Code
-- stock, rating and is_available are left out: defaults (or NULL) fill them
INSERT INTO products (product_name, category_id, price)
VALUES ('Bamboo Cutting Board', 4, 399)
RETURNING product_name, stock, rating, is_available;
 
-- DEFAULT asks for the default by name
INSERT INTO products (product_name, category_id, price, stock)
VALUES ('Glass Jar Set', 4, 699, DEFAULT)
RETURNING product_name, stock;

Several rows

Add a batch of rows in one statement. Oracle allows this from 23ai; older versions use INSERT ALL.

Code
INSERT INTO categories (category_name, description)
VALUES ('Pet Supplies', 'Food and toys for pets'),
       ('Music', 'Instruments and accessories');

Without a column list

The column list can be left out, but then you must give a value for every column, in the table's own order. DEFAULT fills a column the way the table would.

Code
-- category_id, category_name, description, parent_category_id
INSERT INTO categories VALUES (DEFAULT, 'Stationery', 'Pens and notebooks', NULL);
 
-- Fails: the one value goes to the first column, category_id, which is a number
INSERT INTO categories VALUES ('Stationery');

INSERT … SELECT

Add the rows a query finds, instead of typing them.

Code
-- Customer 1 gives every Toys product a 5-star review
INSERT INTO reviews (product_id, customer_id, rating)
SELECT product_id, 1, 5
FROM products
WHERE category_id = 8;

Duplicates: ON CONFLICT

Decide what happens when a row already exists, instead of getting an error.

Code
-- Fails: 'Books' already exists and category_name is UNIQUE
INSERT INTO categories (category_name) VALUES ('Books');
 
-- Skip rows that already exist
INSERT INTO categories (category_name) VALUES ('Books')
ON CONFLICT (category_name) DO NOTHING;
 
-- Or update the existing row with the new value
INSERT INTO products (product_name, category_id, price)
VALUES ('The Alchemist', 2, 399)
ON CONFLICT (product_name, category_id) DO UPDATE SET price = EXCLUDED.price
RETURNING product_name, price;

Remember

Always list the columns. Without the list, values fill the columns in table order from the first one, so a missing or misplaced value lands in the wrong column.
Writing NULL is not the same as leaving a column out: NULL skips the default and fails on a NOT NULL column.
ON CONFLICT needs a unique rule (a primary key or UNIQUE) on the columns you name.
A failed insert can still use up a generated id, so ids can have gaps.
In this lab every run starts from the same data, so rows you add are gone on the next run.

Practice

INSERT

INSERT adds new rows to a table. You name the columns you fill, and every column you leave out gets its default value.

Syntax

If any row breaks a rule, the whole statement fails and no row is added.

ON CONFLICT and RETURNING are PostgreSQL's. MySQL handles duplicates with ON DUPLICATE KEY UPDATE, SQL Server and Oracle with MERGE, and SQL Server shows the stored rows with OUTPUT.

Code
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...)     -- or: SELECT ...
ON CONFLICT (column) DO NOTHING | DO UPDATE SET column = EXCLUDED.column
RETURNING column1, column2, ...;

Part	Meaning
`(column1, column2)`	the columns you fill, in the same order as the values
`VALUES (...), (...)`	one bracket per row
`DEFAULT`	use the column's default for this value
`SELECT ...`	add the rows a query returns, instead of `VALUES`
`ON CONFLICT (column)`	what to do when a row would repeat a unique value
`EXCLUDED.column`	the value you tried to insert
`RETURNING`	show the stored rows, like a `SELECT`

Examples

One row

Add a single record, filling only the columns you name.

Code
INSERT INTO categories (category_name, description)
VALUES ('Garden', 'Plants, pots and tools');
 
-- Text goes in single quotes, numbers don't
INSERT INTO products (product_name, category_id, price)
VALUES ('Steel Bottle 1L', 4, 549);

Defaults and RETURNING

See the values the database filled in for you.

Code
-- stock, rating and is_available are left out: defaults (or NULL) fill them
INSERT INTO products (product_name, category_id, price)
VALUES ('Bamboo Cutting Board', 4, 399)
RETURNING product_name, stock, rating, is_available;
 
-- DEFAULT asks for the default by name
INSERT INTO products (product_name, category_id, price, stock)
VALUES ('Glass Jar Set', 4, 699, DEFAULT)
RETURNING product_name, stock;

Several rows

Add a batch of rows in one statement. Oracle allows this from 23ai; older versions use INSERT ALL.

Code
INSERT INTO categories (category_name, description)
VALUES ('Pet Supplies', 'Food and toys for pets'),
       ('Music', 'Instruments and accessories');

Without a column list

The column list can be left out, but then you must give a value for every column, in the table's own order. DEFAULT fills a column the way the table would.

Code
-- category_id, category_name, description, parent_category_id
INSERT INTO categories VALUES (DEFAULT, 'Stationery', 'Pens and notebooks', NULL);
 
-- Fails: the one value goes to the first column, category_id, which is a number
INSERT INTO categories VALUES ('Stationery');

INSERT … SELECT

Add the rows a query finds, instead of typing them.

Code
-- Customer 1 gives every Toys product a 5-star review
INSERT INTO reviews (product_id, customer_id, rating)
SELECT product_id, 1, 5
FROM products
WHERE category_id = 8;

Duplicates: ON CONFLICT

Decide what happens when a row already exists, instead of getting an error.

Code
-- Fails: 'Books' already exists and category_name is UNIQUE
INSERT INTO categories (category_name) VALUES ('Books');
 
-- Skip rows that already exist
INSERT INTO categories (category_name) VALUES ('Books')
ON CONFLICT (category_name) DO NOTHING;
 
-- Or update the existing row with the new value
INSERT INTO products (product_name, category_id, price)
VALUES ('The Alchemist', 2, 399)
ON CONFLICT (product_name, category_id) DO UPDATE SET price = EXCLUDED.price
RETURNING product_name, price;

Remember

Always list the columns. Without the list, values fill the columns in table order from the first one, so a missing or misplaced value lands in the wrong column.
Writing NULL is not the same as leaving a column out: NULL skips the default and fails on a NOT NULL column.
ON CONFLICT needs a unique rule (a primary key or UNIQUE) on the columns you name.
A failed insert can still use up a generated id, so ids can have gaps.
In this lab every run starts from the same data, so rows you add are gone on the next run.

SQLAdding New Rows

Syntax

Examples

One row

Defaults and RETURNING

Several rows

Without a column list

INSERT … SELECT

Duplicates: ON CONFLICT

Remember

Practice

SQLAdding New Rows

Syntax

Examples

One row

Defaults and RETURNING

Several rows

Without a column list

INSERT … SELECT

Duplicates: ON CONFLICT

Remember

Practice