Inserting Rows

The schema is set up and can be changed. The next question is how data goes into it. The INSERT statement looks like the plainest statement in the course — a table name, a list of values — but it splits in two places: how values bind to columns, and how many transactions the insert happens inside. The second of these can produce a difference of hundreds of times between two scripts that write the same data.

Inserting a Single Row

The statement has two forms. In the column-list form, which value goes to which column is written directly into the statement. In the positional form, the column list is left out and values are matched by the table’s column order.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  email             TEXT UNIQUE,
  registered_at TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO member (member_id, first_name, last_name, email, registered_at)
VALUES (1,'Alice','Kane','[email protected]','2023-02-14');
INSERT INTO member VALUES (2,'Ben','Ortiz','[email protected]','2023-05-30','suspended');
INSERT INTO member VALUES (3,'Clara','Diaz',NULL,'2024-01-09');

SELECT * FROM member;
SQL

Parse error near line 15: table member has 6 columns but 5 values were supplied
member_id  first_name  last_name  email               registered_at  status   
---------  ----------  ---------  ------------------  -------------  ---------
1          Alice       Kane       [email protected]  2023-02-14     active   
2          Ben         Ortiz      [email protected]    2023-05-30     suspended

The first statement did not write to the status column and got the default value. The second gave every column in order. The third used the positional form but sent five values to a table with six columns, and was rejected.

In this example the column list looks like nothing more than a style choice; for maintainability, the two are not equal. When a column is added, or the column order changes, the positional form either errors out or — worse — writes values to the wrong columns because the types happen to match. The ALTER TABLE ADD COLUMN statement from the previous lesson is exactly the kind of change that does this. If the column list is written out, a schema change does not affect the statement. In code meant to last, insert statements are written with a column list; the positional form stays for quick experiments on the command line.

Multiple Rows in a Single Statement

More than one row, separated by commas, can be written after the VALUES keyword. The engine processes this as a single statement.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);

INSERT INTO branch (branch_id, name, city) VALUES
  (1, 'Central',      'Ankara'),
  (2, 'Bahcelievler', 'Ankara'),
  (3, 'Kadikoy',      'Istanbul');

SELECT count(*) AS inserted FROM branch;
SQL

inserted
--------
3       

Being a single statement does not just shorten the syntax, it also determines the behavior: the statement is not split apart. If one of the rows violates a constraint, none of them is written.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO branch VALUES (1,'Central','Ankara');

INSERT INTO branch (branch_id, name, city) VALUES
  (3, 'Kadikoy',      'Istanbul'),
  (1, 'Central',      'Ankara'),
  (4, 'Konak',        'Izmir');

SELECT * FROM branch ORDER BY branch_id;
SQL

Runtime error near line 6: UNIQUE constraint failed: branch.branch_id (19)
branch_id  name     city  
---------  -------  ------
1          Central  Ankara

The conflicting row was second in the list; even so, neither the Kadikoy row before it nor the Konak row after it made it into the table. A statement is applied either in full or not at all — this is the statement-level face of the property called atomicity in the Relational Theory course.

Inserting a Query’s Result

A SELECT can be written in place of VALUES. This form moves data from one table to another without passing it through the application; the read and the write happen in a single statement.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE loan (
  loan_id       INTEGER PRIMARY KEY,
  book_id       INTEGER NOT NULL,
  member_id     INTEGER NOT NULL,
  pickup_date TEXT NOT NULL,
  return_date   TEXT
);
INSERT INTO loan VALUES
  (1,1,1,'2025-01-10','2025-01-24'),
  (2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),
  (4,3,3,'2025-03-01','2025-03-15');

CREATE TABLE loan_archive (
  loan_id    INTEGER PRIMARY KEY,
  book_id    INTEGER NOT NULL,
  member_id  INTEGER NOT NULL,
  day_count  INTEGER NOT NULL
);

INSERT INTO loan_archive (loan_id, book_id, member_id, day_count)
SELECT loan_id, book_id, member_id,
       CAST(julianday(return_date) - julianday(pickup_date) AS INTEGER)
FROM loan
WHERE return_date IS NOT NULL;

SELECT changes() AS transferred;
SELECT * FROM loan_archive;
SQL

transferred
-----------
3          
loan_id  book_id  member_id  day_count
-------  -------  ---------  ---------
1        1        1          14       
2        2        1          18       
4        3        3          14       

The expressions in the SELECT list are matched to the target columns by position; the names do not need to overlap, but the count and types of columns must match. The query’s WHERE condition decides which rows get transferred: the loan that had not been returned was left out.

The changes() call gives the number of rows the last statement affected. Its name differs by engine, but every engine has a counterpart, and it is used in bulk operations to confirm the expected count. julianday is a date function that computes the difference in days, and CAST converts the result to an integer.

Reading Back an Inserted Row

When the engine generates the primary key, the key of an inserted row is not known after the insert. Many engines allow an output list to be attached to the insert statement for exactly this.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id         INTEGER PRIMARY KEY,
  first_name        TEXT NOT NULL,
  last_name         TEXT NOT NULL,
  registered_at TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO member (first_name, last_name, registered_at)
VALUES ('Derek','Voss','2024-03-22') RETURNING member_id, first_name, status;
INSERT INTO member (first_name, last_name, registered_at)
VALUES ('Grace','Kim','2024-11-05') RETURNING member_id, first_name, status;
SQL

member_id  first_name  status
---------  ----------  ------
1          Derek       active
member_id  first_name  status
---------  ----------  ------
2          Grace       active

The key column was never written; the engine generated it and gave it back in the statement’s output together with the default status. The RETURNING syntax differs by engine: some support it, others offer a separate function that returns the last generated key. When the key is needed right after an insert, the first question to ask is which way the engine in use provides it.

What Happens on Conflict

The default behavior when an insert uses a key that already exists is an error. In some jobs, what is wanted instead is “update if it exists, insert if it does not.”

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
INSERT INTO branch VALUES (1,'Central','Ankara');

INSERT INTO branch (branch_id, name, city) VALUES (1, 'Central Branch', 'Ankara')
  ON CONFLICT (branch_id) DO UPDATE SET name = excluded.name;

INSERT INTO branch (branch_id, name, city) VALUES (1, 'Will Be Ignored', 'Ankara')
  ON CONFLICT (branch_id) DO NOTHING;

SELECT * FROM branch;
SQL

branch_id  name            city  
---------  --------------  ------
1          Central Branch  Ankara

The first statement caught the conflict and updated the name; the second silently ignored the conflict. excluded names the row that was being inserted but could not be written because of the conflict.

The name and detail of this syntax differ by engine; some do the same job with a different keyword. What does not change is that writing the conflict behavior into the statement is safer than writing “query first, then insert” in application code: in the time between the query and the insert, another session can insert the same row.

Batching Inserts Inside a Transaction

How many transactions the insert statements run inside is what separates two scripts writing the same data. If no explicit transaction is started, the engine treats every statement as its own transaction and syncs to disk at the end of each one to guarantee durability. Twenty thousand statements means twenty thousand disk syncs.

cd "$(mktemp -d)"

generate_rows() {
  awk 'BEGIN { for (i = 1; i <= 20000; i++) printf "INSERT INTO loan_load VALUES (%d, %d, %d, \0472025-03-01\047);\n", i, 1 + i % 7, 1 + i % 6 }'
}
schema="CREATE TABLE loan_load (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL);"

{ echo "$schema"; generate_rows; }                               > per_statement.sql
{ echo "$schema"; echo "BEGIN;"; generate_rows; echo "COMMIT;"; } > single_transaction.sql

echo "--- each statement its own transaction ---"
time sqlite3 a.db ".read per_statement.sql"
echo "--- all in a single transaction ---"
time sqlite3 b.db ".read single_transaction.sql"
sqlite3 a.db "SELECT count(*) FROM loan_load;"
sqlite3 b.db "SELECT count(*) FROM loan_load;"

--- each statement its own transaction ---

real	0m3.758s
user	0m0.148s
sys	0m2.785s
--- all in a single transaction ---

real	0m0.025s
user	0m0.021s
sys	0m0.002s
20000
20000

Same twenty thousand rows, same statements, same result. The difference between them is hundreds of times over. The measurement depends on the disk and the machine’s characteristics — the numbers change on every run and every machine — but the size of the ratio shows how decisive the transaction boundary is for performance. The \047 sequence inside awk produces a single-quote character; it lets a SQL string be written without colliding with the shell’s own quoting.

The same effect can also be reached by writing multiple rows in a single statement: a thousand-row VALUES list is already a single transaction. The two techniques are used together — batched statements, inside an explicit transaction. Keeping the transaction boundary too wide also has a cost, and that subject belongs to the locking lesson in the Advanced SQL course.

Summary

• The column-list form of the insert statement holds up against schema changes; the positional form can silently write to the wrong column when the column order changes.
• Multiple rows written in a single statement are not split apart: if one of the rows runs into a constraint, none of them is written.
• The INSERT ... SELECT form transfers data between tables without passing it through the application; the mapping is done by position, not by column name.
• There is a way to read back an inserted row’s engine-generated values and to specify conflict behavior in the statement, but the syntax differs by engine.
• Batching inserts inside an explicit transaction is measurably faster than treating every statement as its own transaction; the difference comes from the number of disk syncs.

Next Step

The data is in place. The next lesson takes up changing and deleting it. The syntax of UPDATE and DELETE is short, and the danger comes from exactly that: the condition clause is optional. An update that forgets its condition is syntactically flawless and changes every row in the table. That lesson’s core is a habit to run while writing these statements — open a transaction first, see the number of rows affected, and roll back if needed.