SQLite INSERT

The INSERT statement adds new rows to a table. Every piece of data you store in SQLite enters through an INSERT statement. Knowing every form of INSERT gives you full control over how data lands in your tables.

Basic INSERT Syntax

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

Setting Up a Sample Table

CREATE TABLE IF NOT EXISTS products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  category TEXT,
  price    REAL,
  in_stock INTEGER DEFAULT 1
);

Insert One Row

INSERT INTO products (name, category, price)
VALUES ('Notebook', 'Stationery', 2.99);

-- Result:
id │ name       │ category    │ price │ in_stock
───┼────────────┼─────────────┼───────┼──────────
1  │ Notebook   │ Stationery  │ 2.99  │ 1
   ↑ auto-id    ↑ from insert             ↑ default

Insert All Columns (Column List Optional)

When you supply values for every column in the exact order they were defined, you can omit the column list. This is shorter but fragile — adding or reordering columns later breaks the statement.

-- Omitting column names (provide ALL values in order)
INSERT INTO products VALUES (NULL, 'Pen', 'Stationery', 0.99, 1);

-- NULL for the id lets SQLite auto-assign it

Insert Multiple Rows at Once

Inserting many rows in a single statement is faster than separate inserts because SQLite processes one transaction instead of many.

INSERT INTO products (name, category, price)
VALUES
  ('Ruler',       'Stationery', 1.49),
  ('Eraser',      'Stationery', 0.59),
  ('Backpack',    'Bags',       24.99),
  ('Water Bottle','Accessories',8.50);

-- Four rows added in one statement

INSERT with SELECT (Copy from Another Table)

You can pull data from one table and insert it into another in a single statement.

-- Copy all stationery items into an archive table
CREATE TABLE archived_products AS
  SELECT * FROM products WHERE 1=0;   -- empty copy of structure

INSERT INTO archived_products
  SELECT * FROM products
  WHERE category = 'Stationery';

INSERT OR REPLACE

When a row with the same primary key or unique value already exists, normal INSERT fails. INSERT OR REPLACE removes the old row and inserts the new one.

-- id=1 already exists
INSERT OR REPLACE INTO products (id, name, category, price)
VALUES (1, 'Notebook Deluxe', 'Stationery', 4.99);

-- Old row with id=1 is REPLACED entirely

INSERT OR IGNORE

This silently skips the insert when a constraint violation would occur. Useful for bulk imports where some rows may already exist.

INSERT OR IGNORE INTO products (id, name, price)
VALUES (1, 'Duplicate Product', 9.99);

-- Nothing happens if id=1 exists. No error thrown.

Checking Rows After INSERT

sqlite> SELECT * FROM products;
id │ name          │ category     │ price │ in_stock
───┼───────────────┼──────────────┼───────┼──────────
1  │ Notebook      │ Stationery   │ 2.99  │ 1
2  │ Pen           │ Stationery   │ 0.99  │ 1
3  │ Ruler         │ Stationery   │ 1.49  │ 1
4  │ Eraser        │ Stationery   │ 0.59  │ 1
5  │ Backpack      │ Bags         │ 24.99 │ 1
6  │ Water Bottle  │ Accessories  │ 8.50  │ 1

Last Inserted Row ID

After an insert, you can retrieve the ID of the row just added.

INSERT INTO products (name, price) VALUES ('Scissors', 3.25);

-- Get the ID of the last inserted row:
SELECT last_insert_rowid();
-- Returns: 7  (or whatever the next id was)

INSERT vs Direct Edit

USING INSERT (correct)         DIRECT FILE EDIT (wrong)
───────────────────────────    ──────────────────────────────
INSERT INTO products ...       Open .db in text editor
SQL is validated               No validation
Constraints enforced           Constraints bypassed
Transactions supported         Risk of file corruption

Key Takeaways

• Use INSERT INTO table (cols) VALUES (vals) to add one row
• Insert multiple rows by listing multiple value groups separated by commas
• INSERT OR REPLACE overwrites existing rows on conflict
• INSERT OR IGNORE skips conflicting rows silently
• last_insert_rowid() retrieves the ID of the most recently inserted row