Insert Data

INSERT adds new rows to a table.

The safest pattern is to always specify column names so schema changes do not silently break your queries.

Core Concepts

flowchart LR
  A[INSERT statement] --> B[Column list]
  B --> C[VALUES list]
  C --> D[New row added]

Pattern	Example	Why it helps
Explicit columns	INSERT INTO users(name,email) VALUES(...);	Prevents order mistakes
Omit optional fields	Leave out columns with defaults	Cleaner inserts

Recommended Pattern

-- Setup table for examples.
CREATE TABLE users (
  user_id     INTEGER PRIMARY KEY,
  full_name   TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  active      INTEGER DEFAULT 1
);

-- Insert with explicit column list.
INSERT INTO users (full_name, email)
VALUES ('Ada Lovelace', 'ada@example.com');

Expected output
Table created and one row inserted successfully.

Less Safe Pattern

-- Avoid this in production: depends on full table column order.
INSERT INTO users
VALUES (NULL, 'Alan Turing', 'alan@example.com', 1);

Expected output
Row inserts if value order matches schema exactly.

Code Examples

-- Verify inserted rows.
SELECT user_id, full_name, email, active
FROM users;

user_id	full_name	email	active
1	Ada Lovelace	ada@example.com	1
2	Alan Turing	alan@example.com	1

SQLite-Specific Nuances

SQLite Nuance

SQLite can auto-generate values for INTEGER PRIMARY KEY columns when you provide NULL or omit the column.

Common Pitfalls / Best Practices

Pitfall

Using INSERT INTO table VALUES (...) without a column list in long-lived code.

Future schema changes can make value ordering wrong.

Best Practice

Always list target columns in INSERT statements.

Quick Challenge

Insert one new user named Grace Hopper with email grace@example.com using explicit columns.

View Solution

INSERT INTO users (full_name, email)
VALUES ('Grace Hopper', 'grace@example.com');

Expected output
One additional row inserted; active defaults to 1.