SQLite INSERT INTO: Insert Rows, Multiple Rows and SELECT Results (Tested)

Ninad Pathak

October 7, 2026

Rows get into an SQLite table with INSERT, and it has only a handful of forms. It also has a few traps, like constraint errors and a WHERE clause SQLite won’t accept. In this article I’ll walk you through each form with real output.

TLDR

Insert one row with INSERT INTO users (name, age) VALUES ('John', 30);.
Insert many rows at once by listing several value groups after VALUES, separated by commas.

Copy rows from a query with INSERT INTO table (cols) SELECT .... SQLite has no SELECT ... INTO.
INSERT has no WHERE clause. Filter in the SELECT part, or use UPDATE for existing rows.
RETURNING and last_insert_rowid() give you the new row’s ID.

What is the SQLite INSERT statement?

INSERT adds new rows to a table. SQLite’s documentation lists three basic forms (SQLite INSERT):

INSERT INTO ... VALUES, which inserts the rows you write out;
INSERT INTO ... SELECT, which inserts the rows a query returns;

INSERT INTO ... DEFAULT VALUES, which inserts one row made of column defaults.

SQLite INSERT syntax

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

INSERT INTO table_name (column1, column2, ...)
SELECT ... FROM other_table WHERE ...;

INSERT INTO table_name DEFAULT VALUES;

Part	What it does
`(column1, column2, ...)`	The columns you’re filling. Leave it out only when you supply a value for every column, in table order
`VALUES (...), (...)`	One parenthesised group per row
`SELECT ...`	Inserts every row the query returns
`OR IGNORE`, `OR REPLACE`	What to do when a row breaks a constraint (conflict clause)
`RETURNING ...`	Returns columns of the inserted rows (SQLite 3.35.0 and later, RETURNING)

How to insert data into an SQLite table

To show you each form, I will use a small users table. Its joined column defaults to today’s date.

Step 1: Create the table

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, age INTEGER, joined TEXT DEFAULT CURRENT_DATE);

See SQLite CREATE TABLE for the column options.

Step 2: Insert one row

Name the columns, then give a value for each. The id column is an INTEGER PRIMARY KEY, so SQLite fills it in:

sqlite> INSERT INTO users (name, age) VALUES ('John', 30);
sqlite> SELECT id, name, age FROM users;
id  name  age
--  ----  ---
1   John  30

Step 3: Insert multiple rows in one statement

List several value groups, separated by commas:

sqlite> INSERT INTO users (name, age) VALUES ('Mike', 40), ('Sara', 25), ('Ravi', 33);
sqlite> SELECT id, name, age FROM users;
id  name  age
--  ----  ---
1   John  30
2   Mike  40
3   Sara  25
4   Ravi  33

A single statement for many rows is shorter to write, and SQLite runs it as one statement, so either every row goes in or none does.

Examples

Each example below continues with the same users table, so the IDs carry on from the steps above.

Insert without listing the columns

If you leave out the column list, you must give a value for every column, in the order the table defines them. NULL in the id position lets SQLite choose the ID:

sqlite> INSERT INTO users VALUES (NULL, 'Lena', 29, '2026-01-15');
sqlite> SELECT * FROM users WHERE name = 'Lena';
id  name  age  joined
--  ----  ---  ----------
5   Lena  29   2026-01-15

Miss one value and SQLite refuses the statement:

sqlite> INSERT INTO users VALUES (NULL, 'Omar', 50);
Error: in prepare, table users has 4 columns but 3 values were supplied

Naming the columns avoids this, and keeps working if someone adds a column later.

Let defaults fill the columns you skip

Columns you don’t name get their DEFAULT, or NULL if they have none. Here age has no default, and joined gets today’s date:

sqlite> INSERT INTO users (name) VALUES ('Kim');
sqlite> SELECT name, age, joined IS NOT NULL AS has_joined_date FROM users WHERE name = 'Kim';
name  age  has_joined_date
----  ---  ---------------
Kim        1

To insert a row made only of defaults, use DEFAULT VALUES:

sqlite> CREATE TABLE visits (id INTEGER PRIMARY KEY, at TEXT DEFAULT CURRENT_TIMESTAMP);
sqlite> INSERT INTO visits DEFAULT VALUES; SELECT id, at IS NOT NULL AS has_time FROM visits;
id  has_time
--  --------
1   1

Get the ID of the new row

RETURNING sends back columns from the row you inserted:

sqlite> INSERT INTO users (name, age) VALUES ('Noor', 31) RETURNING id, name;
id  name
--  ----
7   Noor

On older SQLite versions, call last_insert_rowid() on the same connection right after the insert:

sqlite> INSERT INTO users (name, age) VALUES ('Tom', 45); SELECT last_insert_rowid();
last_insert_rowid()
-------------------
8

Insert rows from a SELECT

INSERT INTO ... SELECT copies the rows a query returns. The WHERE goes in the SELECT:

sqlite> CREATE TABLE seniors (name TEXT, age INTEGER);
sqlite> INSERT INTO seniors (name, age) SELECT name, age FROM users WHERE age >= 40;
sqlite> SELECT * FROM seniors;
name  age
----  ---
Mike  40
Tom   45

SQLite INSERT INTO SELECT covers this form in depth.

Insert from Python

In Python, pass values as parameters with ? placeholders, never by formatting them into the SQL string. executemany() inserts a list of rows:

import sqlite3

con = sqlite3.connect("t.db")
new_users = [("Grace", 36), ("Linus", 28)]
with con:  # commits on success
    con.executemany("INSERT INTO users (name, age) VALUES (?, ?)", new_users)
    cur = con.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Ada", 41))
print("last row id:", cur.lastrowid)
print(con.execute("SELECT count(*) FROM users").fetchone()[0], "users")
con.close()

$ python3 insert_py.py
last row id: 11
11 users

Edge cases

Each of these is an INSERT that fails or does something you might not expect.

INSERT has no WHERE clause

A new row doesn’t exist yet, so there’s nothing for WHERE to filter:

sqlite> INSERT INTO seniors (name, age) VALUES ('Zed', 70) WHERE age > 60;
Error: in prepare, near "WHERE": syntax error
  NSERT INTO seniors (name, age) VALUES ('Zed', 70) WHERE age > 60;
                                      error here ---^

To insert only some rows of another table, put the WHERE in an INSERT ... SELECT. To change rows that already exist, use UPDATE (SQLite UPDATE).

SELECT INTO doesn’t exist in SQLite

SQL Server’s SELECT ... INTO new_table is a syntax error in SQLite:

sqlite> SELECT name, age INTO backup_users FROM users;
Error: in prepare, near "INTO": syntax error
  SELECT name, age INTO backup_users FROM users;
                   ^--- error here

Use CREATE TABLE ... AS SELECT instead:

sqlite> CREATE TABLE backup_users AS SELECT name, age FROM users; SELECT count(*) FROM backup_users;
count(*)
--------
11

Duplicate and missing values

A UNIQUE or NOT NULL column rejects rows that break it:

sqlite> INSERT INTO users (name, age) VALUES ('John', 31);
Error: stepping, UNIQUE constraint failed: users.name (19)

sqlite> INSERT INTO users (age) VALUES (22);
Error: stepping, NOT NULL constraint failed: users.name (19)

INSERT OR IGNORE skips the row that breaks a constraint instead of failing. changes() returns 0, and John’s row keeps its old age:

sqlite> INSERT OR IGNORE INTO users (name, age) VALUES ('John', 31); SELECT changes();
changes()
---------
0
sqlite> SELECT name, age FROM users WHERE name = 'John';
name  age
----  ---
John  30

To update the existing row instead of skipping it, use an upsert. See SQLite upsert.

Conclusion

Use INSERT ... VALUES with a column list for rows you write out, and list several value groups to add many rows at once. Use INSERT ... SELECT to copy rows from a query, with the filter in the SELECT. Reach for INSERT OR IGNORE or an upsert when rows may already exist.