Adds one or more rows to a table. Rows can be specified as literal values, copied from another query, or generated from column defaults.
Syntax
INSERT [OR conflict_action] INTO table_name [(column_name [, ...])]
VALUES (expression [, ...]) [, (expression [, ...]) ...]
[RETURNING result_column [, ...]];
INSERT [OR conflict_action] INTO table_name [(column_name [, ...])]
select_statement
[RETURNING result_column [, ...]];
INSERT [OR conflict_action] INTO table_name [(column_name [, ...])]
DEFAULT VALUES
[RETURNING result_column [, ...]];
Parameters
| Parameter | Description |
|---|
conflict_action | One of REPLACE, IGNORE, ABORT, ROLLBACK, or FAIL. Controls behavior on constraint violations. |
table_name | The table to insert rows into. |
column_name | Column to assign a value to. Unlisted columns receive their default value or NULL. |
expression | A value to insert. Must match the position or name of the target column. |
select_statement | A query whose result rows are inserted into the table. |
result_column | Column or expression to return for each inserted row. |
Description
INSERT adds new rows to the specified table. There are three forms:
- VALUES — insert one or more rows with explicit values.
- SELECT — insert the result set of a query.
- DEFAULT VALUES — insert a single row where every column uses its default value (or NULL if no default is defined).
When a column list is provided, only those columns receive explicit values. All other columns use their default value or NULL. When no column list is provided, values must be supplied for every column in the table, in the order the columns were defined.
Inserting rows
Single row
INSERT INTO employees (name, department, salary)
VALUES ('Alice', 'Engineering', 95000);
Multiple rows
Supply multiple parenthesized groups separated by commas.
INSERT INTO employees (name, department, salary) VALUES
('Bob', 'Design', 82000),
('Carol', 'Engineering', 97000),
('Dave', 'Marketing', 74000);
All columns
When the column list is omitted, provide a value for every column in table-definition order.
-- Assuming employees has columns: id, name, department, salary
INSERT INTO employees VALUES (1, 'Alice', 'Engineering', 95000);
INSERT INTO … SELECT
Inserts the result of a SELECT statement. The number of columns returned by the SELECT must match the number of target columns.
INSERT INTO archive_employees (name, department, salary)
SELECT name, department, salary
FROM employees
WHERE termination_date IS NOT NULL;
SELECT can include any valid clause: WHERE, JOIN, GROUP BY, ORDER BY, LIMIT, UNION, or subqueries.
INSERT INTO monthly_summary (department, headcount, total_salary)
SELECT department, COUNT(*), SUM(salary)
FROM employees
GROUP BY department;
DEFAULT VALUES
Inserts a single row where every column receives its default value. Columns without an explicit DEFAULT definition receive NULL. INTEGER PRIMARY KEY columns receive an auto-generated rowid.
CREATE TABLE audit_log (
id INTEGER PRIMARY KEY,
created_at TEXT DEFAULT (datetime('now')),
action TEXT DEFAULT 'unknown'
);
INSERT INTO audit_log DEFAULT VALUES;
-- Inserts: (auto_id, current_timestamp, 'unknown')
Column default values
When a column list is provided, omitted columns use their default values.
CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
status TEXT DEFAULT 'pending',
priority INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
-- status, priority, and created_at use their defaults
INSERT INTO tasks (title) VALUES ('Review pull request');
Conflict handling
The OR clause specifies what happens when an INSERT violates a uniqueness or NOT NULL constraint.
| Action | Behavior |
|---|
ABORT | Roll back the current statement and return an error. Previously inserted rows within the same INSERT are undone. This is the default. |
ROLLBACK | Roll back the entire transaction and return an error. |
FAIL | Stop execution and return an error. Rows already inserted by this statement before the failure are kept. |
IGNORE | Skip the row that caused the violation and continue with the next row. |
REPLACE | Delete the conflicting row and insert the new row in its place. |
-- Skip rows that violate a UNIQUE constraint
INSERT OR IGNORE INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'Engineering', 95000);
-- Replace the existing row on conflict
INSERT OR REPLACE INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'Engineering', 105000);
-- Roll back only this statement on conflict
INSERT OR ABORT INTO employees (id, name, department, salary)
VALUES (1, 'Alice', 'Engineering', 95000);
For more granular conflict handling based on specific constraints, use the ON CONFLICT (UPSERT) clause, which allows different actions depending on which constraint was violated.
RETURNING clause
Returns data from the rows that were actually inserted. Useful for retrieving generated values such as auto-incremented IDs or evaluated defaults.
INSERT INTO employees (name, department, salary)
VALUES ('Eve', 'Engineering', 91000)
RETURNING id, name;
-- Returns: 5|Eve
RETURNING accepts any expression that can reference the inserted row’s columns.
INSERT INTO employees (name, department, salary)
VALUES ('Frank', 'Design', 78000)
RETURNING id, name, salary * 12 AS annual_salary;
-- Returns: 6|Frank|936000
RETURNING with multiple rows
INSERT INTO employees (name, department, salary) VALUES
('Grace', 'Engineering', 102000),
('Hank', 'Marketing', 68000)
RETURNING id, name, department;
-- Returns one row per inserted row
RETURNING *
INSERT INTO employees (name, department, salary)
VALUES ('Ivy', 'Product', 88000)
RETURNING *;
Examples
Insert with subquery values
INSERT INTO department_stats (department, avg_salary)
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;
Insert with conflict handling and RETURNING
INSERT OR REPLACE INTO settings (key, value)
VALUES ('theme', 'dark'), ('locale', 'en-US')
RETURNING key, value;
Archival pattern
-- Copy rows to archive, then delete originals
INSERT INTO orders_archive
SELECT * FROM orders WHERE order_date < '2023-01-01';
DELETE FROM orders WHERE order_date < '2023-01-01';
See also