INSERT Statement

Add new rows — one, many, or copied from another query

Beginner 12 minDMLINSERTVALUES

Adding Rows to a Table

Once a table exists, INSERT is how rows get into it. There are two primary patterns: literal values and query results. Here's what you need to know:

Literal values — INSERT INTO t (col1, col2) VALUES (v1, v2) is the simplest form. You list which columns you're providing, then the values in matching order. Columns not in your list get their DEFAULT (or NULL).

Multi-row insert — INSERT INTO t (cols) VALUES (r1), (r2), (r3) inserts three rows in a single statement. Much faster than three separate INSERTs, and atomic — either all succeed or all fail. Works in SQLite, PostgreSQL, MySQL, SQL Server (since 2008).

INSERT ... SELECT — INSERT INTO archive SELECT * FROM orders WHERE status = 'cancelled' copies rows from a query result directly into the target table. This is the foundation of ETL, archival jobs, and populating staging tables.

Always name columns — INSERT INTO t VALUES (1, 'x', 2) works *today*, but the moment someone adds or reorders columns in the table, every unnamed INSERT silently loads data into the wrong columns. Naming columns makes the insert schema-change-proof.

Constraints fire on INSERT — Every NOT NULL, UNIQUE, CHECK, and FOREIGN KEY from the CREATE TABLE is checked. An INSERT that violates any of them is rejected with an error — the table state is unchanged.

Auto-increment — When id INTEGER PRIMARY KEY is declared in SQLite, you can omit the id column from INSERTs and it will be auto-assigned. Postgres uses SERIAL or GENERATED ALWAYS AS IDENTITY. MySQL uses AUTO_INCREMENT.

Knowledge Canvas

How INSERT Works

Add new rows to a table

INSERT INTO t (cols) VALUES (...) — literal insert
Multi-row: VALUES (r1), (r2), (r3)
INSERT INTO t SELECT ... — copy from a query
All constraints from CREATE TABLE are checked

INSERT Traps

Silent data corruption

Positional INSERT (no column list) breaks when table shape changes
UNIQUE violations fail with "constraint failed"
NOT NULL violations also fail — provide a value or DEFAULT
FK violations fail only if foreign_keys pragma is ON (SQLite)

Conflict Handling

When UNIQUE violations happen

SQLite: INSERT OR IGNORE, INSERT OR REPLACE
Postgres: INSERT ... ON CONFLICT DO NOTHING / DO UPDATE SET
MySQL: INSERT IGNORE, ON DUPLICATE KEY UPDATE
All three implement the UPSERT pattern

Bulk Insert Tips

Loading lots of data

Wrap many INSERTs in a single transaction — huge speedup
Drop indexes before bulk load, recreate after
INSERT ... SELECT is faster than looping single-row INSERTs
For CSV loads: use COPY (Postgres) or LOAD DATA (MySQL)

Syntax

Syntax Template

1-- Single row

2INSERT INTO t (col1, col2, col3)

3VALUES (v1, v2, v3);

5-- Multiple rows in one statement

6INSERT INTO t (col1, col2) VALUES

7 (a1, a2),

8 (b1, b2),

9 (c1, c2);

11-- INSERT from a query (ETL pattern)

12INSERT INTO archive_orders

13SELECT id, customer_id, order_date, total

14FROM orders

15WHERE status = 'cancelled';

INSERT INTO t (cols)Always name the target columns explicitly

VALUES (...), (...)Multi-row insert — one statement, one transaction

INSERT INTO t SELECT ...Copy rows from a query result

Sample Data

orders

12 rows

id	customer_id	order_date	status	total
5001	C1001	2024-01-15	delivered	59.98
5002	C1002	2024-02-08	delivered	449
5003	C1001	2024-02-20	shipped	12.5
5004	C1003	2024-03-05	delivered	89.99
5005	C1006	2024-03-15	pending	245
5006	C1005	2024-03-22	delivered	159
5007	C1004	2024-04-02	shipped	45.5
5008	C1009	2024-04-12	cancelled	79
5009	C1012	2024-04-22	delivered	425
5010	C1007	2024-05-05	pending	18.99
5011	C1008	2024-05-18	delivered	134
5012	C1010	2024-06-08	cancelled	65.5

Worked Example

Create a staff table, populate it with three rows in one INSERT, then use INSERT ... SELECT to archive all cancelled orders from the orders table.

SQL

1CREATE TABLE IF NOT EXISTS staff (

2 id INTEGER PRIMARY KEY,

3 name TEXT NOT NULL,

4 role TEXT,

5 salary REAL

6);

8-- Multi-row literal INSERT

9INSERT INTO staff (id, name, role, salary) VALUES

10 (1, 'Aarav', 'Engineer', 90000),

11 (2, 'Sara', 'Designer', 78000),

12 (3, 'James', 'Engineer', 95000);

14-- Verify

15SELECT * FROM staff ORDER BY id;

17-- INSERT ... SELECT — archive cancelled orders into a new table

18CREATE TABLE IF NOT EXISTS cancelled_archive (

19 id INTEGER, customer_id TEXT, order_date DATE, total REAL

20);

21INSERT INTO cancelled_archive

22SELECT id, customer_id, order_date, total

23FROM orders WHERE status = 'cancelled';

25SELECT * FROM cancelled_archive ORDER BY id;

The first INSERT adds 3 staff rows atomically. The second INSERT ... SELECT reads from the orders table, filters to cancelled orders, and inserts the result directly into cancelled_archive. Two cancelled orders exist in the dataset (5008 and 5012), so two rows land in the archive. Run these statements one at a time in the Practice Lab to see each step.

Output

2 rows

id	customer_id	order_date	total
5008	C1009	2024-04-12	79
5012	C1010	2024-06-08	65.5

Common Mistakes

❌

Positional INSERT (no column names)

SQL

1INSERT INTO customers VALUES ('C2000', 'Alex', '[email protected]', 'Paris', 'France');

Works today, but if someone later adds a joined_date column between country and the end, this INSERT silently loads "Paris" into whatever the new column order says it should be. No error, corrupted data.

Always list columns explicitly: INSERT INTO customers (id, name, email, city, country) VALUES (...)

❌

Violating a UNIQUE constraint

Trying to insert a row with a value that already exists in a UNIQUE column (like email) fails with "UNIQUE constraint failed". This is usually what you want — the database is stopping duplicate data — but it means you need to handle the error in application code.

Use INSERT OR IGNORE INTO t ... (SQLite) / INSERT ... ON CONFLICT DO NOTHING (Postgres) / INSERT IGNORE (MySQL) to skip conflicts silently. Or use UPSERT patterns to update existing rows instead.

Key Concepts

1Always list column names explicitly — positional INSERT is fragile

2INSERT ... VALUES (...), (...), (...) adds multiple rows in one statement

3INSERT ... SELECT copies rows from a query into a table

4Constraints from CREATE TABLE are enforced on every INSERT — NOT NULL, UNIQUE, CHECK

Pro Tip

INSERT is the gateway into every table. Getting it right matters: a buggy INSERT silently corrupts data, and bad data is much harder to fix than to prevent.

When to Use

Loading seed data, ETL pipelines copying from staging to production, archiving historical rows, user-submitted form data, backfilling new columns.

CREATE TABLE

UPDATE & DELETE