--- title: 'Inserting Rows' source: 'https://academia.sh/en/courses/sql-fundamentals/inserting-rows' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:51+00:00' license: 'CC BY-SA 4.0' --- # Inserting Rows The column-list and positional forms of the INSERT statement, multiple rows in a single statement, writing a query's result into a table, conflict behavior, and the measured effect of batching inserts inside a transaction. 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. ```sh 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','alice@example.test','2023-02-14'); INSERT INTO member VALUES (2,'Ben','Ortiz','ben@example.test','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 alice@example.test 2023-02-14 active 2 Ben Ortiz ben@example.test 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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." ```sh 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. ```sh 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.