Writing dataINSERT
INSERT
Add rows — single, multi-row, from a SELECT, and getting the inserted rows back.
Syntax
INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...), (value1, value2, ...), ...;| Parameter | Type | Notes |
|---|---|---|
| column1, column2, … | identifiers | The columns being set, in order. Omitted columns get their default value
(or |
| VALUES (...)* | one or more rows | One tuple per row. Comma-separate multiple tuples to insert several rows in one statement. |
Single row
SQL playground
Loading editor…
⌘/Ctrl + Enter
Multiple rows in one statement
One round-trip instead of N — meaningfully faster than N separate INSERTs:
SQL playground
Loading editor…
⌘/Ctrl + Enter
INSERT ... SELECT
Copy or transform rows from one query straight into a table — no round trip through the application at all:
SQL playground
Loading editor…
⌘/Ctrl + Enter
Getting the inserted rows back
RETURNING (Postgres, SQLite 3.35+) skips a follow-up SELECT:
SQL playground
Loading editor…
⌘/Ctrl + Enter
SQL Server's equivalent is OUTPUT inserted.customer_id, inserted.name. MySQL
has neither — use LAST_INSERT_ID() for an auto-increment key, or a follow-up
SELECT.
Gotchas
- Column order in
VALUESmust match the column list, not the table's physical column order — always list columns explicitly rather than relying on positional matching to the table definition. - Omitted columns aren't skipped — they get
DEFAULTorNULL. A missingNOT NULLcolumn with no default fails the whole statement. - Duplicate-key behavior is dialect-specific.
INSERT OR IGNORE(SQLite),INSERT IGNORE(MySQL),ON CONFLICT DO NOTHING(Postgres, SQLite) all silently skip a conflicting row differently — check which your engine uses before assuming "ignore" behaves the same everywhere.
- UPDATE — changing rows that already exist.
- Set operations — UNION for combining the SELECT side of an INSERT ... SELECT.
- NULL handling — what an omitted column with no default actually becomes.
Safety checklist
- confirm every
NOT NULLcolumn either has a default or is explicitly supplied - list columns explicitly rather than relying on table column order
- for bulk loads, prefer one multi-row INSERT (or INSERT ... SELECT) over many single-row statements