INSERT

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	Type	Description
`conflict_action`	keyword	One of REPLACE, IGNORE, ABORT, ROLLBACK, or FAIL. Controls behavior on constraint violations
`table_name`	identifier	The table to insert rows into
`column_name`	identifier	Column to assign a value to. Unlisted columns receive their default value or NULL
`expression`	expression	A value to insert, or the keyword `DEFAULT` to use the column’s default value. Must match the position or name of the target column
`select_statement`	SELECT query	A query whose result rows are inserted into the table
`result_column`	expression	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;

The 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');

DEFAULT Keyword in VALUES

Turso extension — This feature follows the SQL standard (SQL:2016) but is not supported by SQLite.

The DEFAULT keyword can be used in place of any value expression in a VALUES list. It resolves to the column’s default value as defined in the CREATE TABLE statement, or NULL if no default is defined. This is particularly useful when inserting multiple rows where different rows need defaults for different columns — something that cannot be achieved by simply omitting columns from the column list.

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  priority INTEGER DEFAULT 0
);

-- Use DEFAULT for specific columns
INSERT INTO tasks (id, title, status, priority)
VALUES (1, 'Fix bug', DEFAULT, 5);
-- Inserts: (1, 'Fix bug', 'pending', 5)

-- Different columns use DEFAULT in different rows
INSERT INTO tasks (id, title, status, priority) VALUES
  (2, 'Write docs', DEFAULT, 3),
  (3, 'Code review', 'in_progress', DEFAULT);
-- Inserts: (2, 'Write docs', 'pending', 3)
--          (3, 'Code review', 'in_progress', 0)

The DEFAULT keyword is only valid inside INSERT VALUES lists. Using it in other contexts (SELECT, WHERE, UPDATE) produces an error.

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 clause (UPSERT), which allows different actions depending on which constraint was violated.

RETURNING Clause

Returns data from the rows that were actually inserted. This is 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 *

Return all columns of the inserted rows.

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';

Getting Started

Fundamentals

AI and Agents

CLI Reference

SQL Reference

Changelog

INSERT

INSERT

Syntax

Parameters

Description

Inserting Rows

Single Row

Multiple Rows

All Columns

INSERT INTO … SELECT

DEFAULT VALUES

Column Default Values

DEFAULT Keyword in VALUES

Conflict Handling

RETURNING Clause

RETURNING with Multiple Rows

RETURNING *

Examples

Insert with Subquery Values

Insert with Conflict Handling and RETURNING

Archival Pattern

See Also

​INSERT

​Syntax

​Parameters

​Description

​Inserting Rows

​Single Row

​Multiple Rows

​All Columns

​INSERT INTO … SELECT

​DEFAULT VALUES

​Column Default Values

​DEFAULT Keyword in VALUES

​Conflict Handling

​RETURNING Clause

​RETURNING with Multiple Rows

​RETURNING *

​Examples

​Insert with Subquery Values

​Insert with Conflict Handling and RETURNING

​Archival Pattern

​See Also

INSERT

Syntax

Parameters

Description

Inserting Rows

Single Row

Multiple Rows

All Columns

INSERT INTO … SELECT

DEFAULT VALUES

Column Default Values

DEFAULT Keyword in VALUES

Conflict Handling

RETURNING Clause

RETURNING with Multiple Rows

RETURNING *

Examples

Insert with Subquery Values

Insert with Conflict Handling and RETURNING

Archival Pattern

See Also