Data Changes

Insert Rows

Adding Data

INSERT adds new rows to an existing table.

Program

Play the script to watch the task list grow.

insert_rows.sql
Replay: real traced execution (multi-file project)
CREATE TABLE tasks (id INTEGER, title TEXT, done INTEGER);
INSERT INTO tasks VALUES (1, 'write', 0);
INSERT INTO tasks VALUES (2, 'review', 0);
SELECT id, title FROM tasks ORDER BY id;

  1. tasks ← 0 rows

    1CREATE TABLE tasks (id INTEGER, title TEXT, done INTEGER);2INSERT INTO tasks VALUES (1, 'write', 0);
    values this step0 rowstasks

  2. tasks ← 1 row

    1CREATE TABLE tasks (id INTEGER, title TEXT, done INTEGER);2INSERT INTO tasks VALUES (1, 'write', 0);3INSERT INTO tasks VALUES (2, 'review', 0);
    values this step1 rowtasks

  3. tasks ← 2 rows

    2INSERT INTO tasks VALUES (1, 'write', 0);3INSERT INTO tasks VALUES (2, 'review', 0);4SELECT id, title FROM tasks ORDER BY id;
    values this step2 rowstasks

  4. result ← 2 rows

    3INSERT INTO tasks VALUES (2, 'review', 0);4SELECT id, title FROM tasks ORDER BY id;
    values this step2 rowsresult

Follow the Inserts

  1. The first INSERT adds task 1, title write, done 0.
  2. The second INSERT adds task 2, title review, done 0.
  3. The table now has two task rows.
  4. The final SELECT id, title shows only the id and title columns. | moment | visible rows | | --- | --- | | after first insert | (1, write, 0) | | after second insert | (1, write, 0), (2, review, 0) | | final select | (1, write), (2, review) |
mutation A mutation changes stored table data.
row Each `VALUES` tuple becomes one row.
read after write A later `SELECT` sees earlier inserts.

Exercise: insert_rows.sql

Reproduce the final rows write and review, then point to the table state after each INSERT.