Module 5: Modifying Data ⏱ 18 min

INSERT INTO Basics

By the end of this lesson you will be able to:
  • Add new rows to a table with INSERT INTO and an explicit column list
  • Insert several rows in one statement with a multi-row VALUES clause
  • Recognise the classic text-versus-number quoting trap before it bites

Every query so far has only read the database. Real applications do more: a new hire joins, a project opens, a department is created. Each of those is a new row, and you add rows with INSERT INTO.

The seed database is rebuilt fresh for every run, so you can insert freely here without ever breaking anything for the next learner. That freedom is the point — modify the data, run a SELECT to see what happened, and try variations until the shape of the statement is second nature. This lesson is the first that changes data instead of just reading it back.

flowchart LR
  T["INSERT INTO employees"] --> C["(name, role, salary)"]
  C --> V["VALUES ('Pia', 'Engineer', 700000)"]
  V --> R["new row added"]
  style R fill:#0e7490,color:#fff
  style V fill:#1e293b,color:#fff
INSERT names the table and the columns you are filling, then hands over one value per column.

The anatomy of an INSERT

An INSERT names the table, lists the columns you are filling, and hands over one value per column in the same order:

INSERT INTO employees (name, department_id, role, salary, hired_on)
VALUES ('Pia Solberg', 1, 'Engineer', 700000, '2025-01-15');

Read it as "into employees, into these five columns, put these five values." The first value fills the first column, the second the second, and so on — order is the only thing that links them. The column list is optional in SQL, but omitting it is a habit you should unlearn on purpose, and the callout below explains why.

Add one employee, then read them back. Change a value and run it again.
INSERT INTO employees (name, department_id, role, salary, hired_on)
VALUES ('Pia Solberg', 1, 'Engineer', 700000, '2025-01-15');

SELECT name, role, salary FROM employees WHERE name = 'Pia Solberg';

Spell out the column list, every time

You may be tempted to skip the column list and write INSERT INTO employees VALUES (...). Resist it. Without the list, SQL fills the table's columns in the exact order they were defined — id, name, department_id, role, salary, hired_on — and you must supply a value for every one of them, in that order, including the id.

The moment someone adds a column to the table later, that statement quietly inserts into the wrong columns or fails outright. An explicit list says precisely which columns you mean, lets you omit the ones you want left default (like id), and survives schema changes. It costs six words and saves hours of debugging.

Omit the id column and the database assigns the next one automatically.
INSERT INTO employees (name, department_id, role, salary)
VALUES ('Ray Okoro', 1, 'Engineer', 680000);

SELECT id, name, role FROM employees WHERE name = 'Ray Okoro';

Where did the id come from?

Notice that insert gave no id, yet the new row received one. The id column is declared INTEGER PRIMARY KEY, which in SQLite makes it an alias for the table's hidden row number. When you leave it out of an insert, the database hands out the next available value — one larger than the largest id already present.

That is why you usually omit id entirely: handing out unique identifiers is the table's job, not yours. Supply a value by hand only when you need a specific number, such as putting a deleted row back exactly where it was.

flowchart TD
  I["INSERT INTO departments<br/>(id, name, city)"] --> K["VALUES"]
  K --> R1["(6, 'Data', 'Bergen')<br/>row 1"]
  K --> R2["(7, 'Legal', 'Oslo')<br/>row 2"]
  K --> R3["(8, 'People', 'Lisbon')<br/>row 3"]
  R1 --> T["three rows added"]
  R2 --> T
  R3 --> T
  style T fill:#0e7490,color:#fff
  style K fill:#1e293b,color:#fff
One VALUES keyword can carry many bracketed rows — one INSERT becomes many new rows.

Insert several rows at once

Adding rows one at a time is tedious and slow. SQL lets you attach many value sets to a single VALUES, each in its own brackets and separated by commas. One statement, many new rows — which is also how real imports behave when they land a batch.

The column list is written once and applies to every row, so each bracketed group must supply values for those same columns in the same order. This is the shape you reach for whenever you have more than two or three rows to add.

Open three departments in a single statement, then list them.
INSERT INTO departments (id, name, city) VALUES
  (6, 'Data',   'Bergen'),
  (7, 'Legal',   'Oslo'),
  (8, 'People',  'Lisbon');

SELECT id, name, city FROM departments WHERE id >= 6 ORDER BY id;

Text in quotes, numbers bare

Values come in two flavours, and mixing them up is the most common INSERT error. Text values are wrapped in single quotes — 'Pia Solberg', 'Engineer' — while numbers sit bare — 700000, 1. Forget the quotes on a name and SQL thinks Pia is a column name, then complains it does not exist; that error message looks worse than the fix, which is just two keystrokes.

Dates are text, stored as 'YYYY-MM-DD', so they are quoted too. The rule of thumb is simple: if it is not a pure number, wrap it in single quotes.

flowchart LR
  Q["'Pia Solberg'<br/>quoted text"] --> OK1["row added"]
  N["700000<br/>bare number"] --> OK2["row added"]
  B["Pia<br/>no quotes"] --> ERR["error: no such column"]
  style OK1 fill:#0e7490,color:#fff
  style OK2 fill:#0e7490,color:#fff
  style ERR fill:#b45309,color:#fff
Quoted text and bare numbers go in; an unquoted name is read as a column and errors.
Exercise

Add a new employee named Sara Voss to the Engineering department (department_id 1) as an Engineer earning 710000, hired on '2025-04-01'. Use an explicit column list.

INSERT INTO employees
-- name the columns and give the values
;
Exercise

Open three new departments in a single INSERT: (9, 'Design', 'Bergen'), (10, 'Ops', 'Oslo'), and (11, 'Finance', 'Lisbon').

INSERT INTO departments (id, name, city)
VALUES
-- three bracketed rows here
;
Exercise

Add a new department with id 12, name 'Growth', and no city — leave city out of the column list entirely so it becomes NULL. Then confirm the city is missing.

INSERT INTO departments
-- only the columns that have values
;
Exercise

This INSERT fails to run — the text values Mira Kahn and Sales Rep are missing their quotes, so SQL reads them as column names. Fix it so Mira Kahn is hired into the Sales department (id 3) as a Sales Rep on salary 500000.

INSERT INTO employees (name, department_id, role, salary)
VALUES (Mira Kahn, 3, Sales Rep, 500000);
Exercise

Transfer what you have learned to the projects table. Add a new project named 'Iris Dashboard', owned by department 1, with status 'active' and budget 500000. Name its columns explicitly.

INSERT INTO projects
-- which columns does a project have? name them
;

Recap

  • INSERT INTO table (columns) VALUES (values) adds a new row; the values fill the columns in the order you listed them.
  • Always write the column list — it survives schema changes and lets you omit columns like id.
  • One VALUES can carry many rows in separate brackets, separated by commas.
  • Text and dates are quoted with single quotes; numbers are bare.
  • A column you leave out of the list takes its default, usually NULL.

Next you will change rows that already exist — raising salaries, fixing roles, and removing what is no longer needed.

Checkpoint quiz

Why should you always write the column list in an INSERT?

Which of these values must be wrapped in single quotes?

Go deeper — technical resources