The INSERT Statement

Prev Next

With the RecipeBox schema in place, it's time to put data into it. INSERT is the statement that adds new rows to a table. This lesson covers the basic single-row form, inserting several rows at once, and INSERT OR IGNORE for quietly skipping rows that would violate a uniqueness constraint.

Basic INSERT

The standard form names the columns you're supplying and lists their values in the same order. Any column you leave out gets its DEFAULT value, or NULL if it has none and isn't NOT NULL.

INSERT INTO users (username, email) VALUES ('sonu_p', 'sonu@example.com');

INSERT INTO recipes (user_id, title, difficulty, prep_minutes)
VALUES (1, 'Creamy Garlic Pasta', 'easy', 20);

Since id INTEGER PRIMARY KEY auto-generates a value, you almost never list it in the column list — SQLite assigns the next available id on its own.

Inserting Multiple Rows at Once

A single INSERT statement can add several rows in one go by listing more than one set of parenthesized values, separated by commas. This is both more concise and noticeably faster than running one INSERT per row, since it's a single statement.

INSERT INTO users (username, email) VALUES
  ('chef_amara', 'amara@example.com'),
  ('noodle_ninja', 'ninja@example.com'),
  ('weekend_baker', 'baker@example.com');

INSERT INTO ingredients (recipe_id, name, quantity) VALUES
  (1, 'spaghetti', '200g'),
  (1, 'garlic', '4 cloves'),
  (1, 'heavy cream', '150ml');

INSERT OR IGNORE — Silently Skip Duplicates

Normally, inserting a row that violates a UNIQUE constraint raises an error and aborts the statement. INSERT OR IGNORE changes that behavior for that one statement: instead of erroring, SQLite just skips the offending row and moves on to the next one, leaving the rest of the table untouched.

-- username is UNIQUE; 'chef_amara' already exists from above
INSERT OR IGNORE INTO users (username, email) VALUES
  ('chef_amara', 'amara-new-email@example.com'),
  ('sourdough_sam', 'sam@example.com');

-- Result: chef_amara's row is left exactly as it was (skipped, not overwritten),
-- and sourdough_sam is inserted normally as a new row.

Note: INSERT OR IGNORE only skips the row — it never updates the existing one. If you actually want to update the existing row when a conflict happens (for example, updating a review instead of erroring when the same user reviews the same recipe twice), that's a job for INSERT OR REPLACE or, better, the ON CONFLICT ... DO UPDATE clause — both covered in full two lessons from now in UPSERT (ON CONFLICT).

Putting It Together

Seeding a small, consistent slice of RecipeBox data in a few statements:

INSERT INTO users (username, email) VALUES ('chef_amara', 'amara@example.com');

INSERT INTO recipes (user_id, title, difficulty, prep_minutes) VALUES
  (1, 'Creamy Garlic Pasta', 'easy', 20),
  (1, 'Slow-Roasted Tomato Soup', 'medium', 45);

INSERT OR IGNORE INTO users (username, email) VALUES ('chef_amara', 'duplicate@example.com');
-- No error, no change - chef_amara already exists

Next: Data is in the tables now — head to SELECT Basics to start reading it back out.

Prev Next