SQL INSERT INTO: Add New Rows to a Table

So far you have only read data. INSERT is how data gets into a table in the first place: every new customer, order, or message in an app is an INSERT behind the scenes.

The example table#

customers

Syntax#

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

• List the columns you are filling, in any order.
• List the values in the same order as the columns.
• Text and dates go in single quotes; numbers do not.

Insert one row#

INSERT INTO customers (customer_id, name, city)
VALUES (5, 'Kiran', 'Hyderabad');

SELECT * FROM customers;

customer_id | name  | city
------------+-------+----------
1           | Asha  | Mumbai
2           | Ravi  | Delhi
3           | Meera | Pune
4           | John  | Chennai
5           | Kiran | Hyderabad

Insert several rows at once#

Separate each row's values with a comma. This is faster than many single inserts:

INSERT INTO customers (customer_id, name, city)
VALUES (6, 'Neha', 'Kolkata'),
       (7, 'Arjun', 'Jaipur');

customer_id | name  | city
------------+-------+--------
6           | Neha  | Kolkata
7           | Arjun | Jaipur

Multi-row VALUES works in MySQL, PostgreSQL, SQLite and SQL Server 2008+. Oracle uses INSERT ALL or one INSERT per row.

Leaving columns out#

Columns you do not list receive their default value, or NULL if they have no default:

INSERT INTO customers (customer_id, name)
VALUES (5, 'Kiran');

customer_id | name  | city
------------+-------+-----
5           | Kiran | NULL

If a column is declared NOT NULL without a default, leaving it out causes an error. That is a constraint doing its job.

Auto-generated ids#

In real tables you rarely type the id. The primary key is usually generated automatically, so you leave it out:

-- id is generated by the database
INSERT INTO customers (name, city)
VALUES ('Kiran', 'Hyderabad');

How a column becomes auto-generated differs by database: SERIAL or GENERATED ALWAYS AS IDENTITY in PostgreSQL, AUTO_INCREMENT in MySQL, IDENTITY in SQL Server, and INTEGER PRIMARY KEY in SQLite. You will see this in CREATE TABLE.

Copy rows with INSERT INTO ... SELECT#

Instead of VALUES, you can insert the result of a query. Suppose a vip_customers table should contain every customer with an order over 200:

INSERT INTO vip_customers (customer_id, name)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 200;

SELECT * FROM vip_customers;

customer_id | name
------------+-----
1           | Asha
2           | Ravi

The selected columns must match the target columns in number and type. This is common for archiving data or building summary tables.

What can go wrong#

Inserting a primary key that already exists fails:

INSERT INTO customers (customer_id, name, city)
VALUES (1, 'Duplicate', 'Agra');

ERROR: UNIQUE constraint failed: customers.customer_id

The exact wording varies by database (PostgreSQL says duplicate key value violates unique constraint), but the meaning is the same: the row was rejected and nothing was inserted.

Common mistakes#

Practice#

INSERT INTO customers (customer_id, name, city)
VALUES (8, 'Sana', 'Lucknow'),
       (9, 'Dev', NULL);

SELECT *
FROM customers
WHERE customer_id IN (8, 9);

customer_id | name | city
------------+------+--------
8           | Sana | Lucknow
9           | Dev  | NULL

Key takeaway#