Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 37 of 49

INSERT: adding rows to a table

Add rows to a table with SQL INSERT, including several rows at once, NULL values, INSERT ... SELECT, and what happens when a constraint rejects a row.

13 minChanging dataUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonAdd one or many rows with INSERT INTO, copy rows from a query with INSERT ... SELECT, and read the error when a constraint rejects a row.

What you will be able to do

After this lesson you can add new data to a table, add many rows in one statement, copy rows from one query into a table, and make sense of the error when the database says no. Every change in this lesson is shown with a SELECT before and after, so you can see the effect.

The basic INSERT

The form is:

text
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

The values are matched to the columns by position. First look at the table as it is now:

sql
SELECT id, name, city FROM departments ORDER BY id;
result
+----+-------------+-----------+
| id |    name     |   city    |
+----+-------------+-----------+
|  1 | Engineering | Pune      |
|  2 | Sales       | Mumbai    |
|  3 | Marketing   | Delhi     |
|  4 | Finance     | Mumbai    |
|  5 | Support     | Pune      |
|  6 | Research    | Bengaluru |
+----+-------------+-----------+

Now add a department and look again:

sql
INSERT INTO departments (id, name, city)
VALUES (7, 'Legal', 'Chennai');

SELECT id, name, city FROM departments ORDER BY id;
result
+----+-------------+-----------+
| id |    name     |   city    |
+----+-------------+-----------+
|  1 | Engineering | Pune      |
|  2 | Sales       | Mumbai    |
|  3 | Marketing   | Delhi     |
|  4 | Finance     | Mumbai    |
|  5 | Support     | Pune      |
|  6 | Research    | Bengaluru |
|  7 | Legal       | Chennai   |
+----+-------------+-----------+

The INSERT itself prints nothing in most tools; the second SELECT is what proves the row exists. Always list the columns, as above. Leaving them out works only if you supply a value for every column in the table's exact order, and it breaks silently the day someone adds a column.

Text and dates are written in single quotes; numbers are not. Dates are safest in the YYYY-MM-DD form.

Several rows at once

Repeat the value list, separated by commas. One statement is faster than many and either adds all rows or none:

sql
SELECT COUNT(*) AS employees_before FROM employees;

INSERT INTO employees (id, first_name, last_name, email, department_id, manager_id, job_title, salary, hire_date)
VALUES
  (21, 'Nikhil', 'Bhat', 'nikhil.bhat@example.com', 7, NULL, 'Legal Counsel', 105000, '2026-11-02'),
  (22, 'Leela',  'Rao',  'leela.rao@example.com',   7, 21,   'Paralegal',      54000, '2026-11-09');

SELECT id, first_name, department_id, manager_id, job_title FROM employees WHERE id > 20 ORDER BY id;
result
+------------------+
| employees_before |
+------------------+
|               20 |
+------------------+
+----+------------+---------------+------------+---------------+
| id | first_name | department_id | manager_id |   job_title   |
+----+------------+---------------+------------+---------------+
| 21 | Nikhil     |             7 | NULL       | Legal Counsel |
| 22 | Leela      |             7 |         21 | Paralegal     |
+----+------------+---------------+------------+---------------+

The new department 7 from the previous step is used here, which is why the foreign key accepts it. NULL is written without quotes and means "no value". Nikhil has no manager, so his manager_id is NULL.

Columns you leave out

If you leave a column out of the list, it takes its default value, or NULL when it has none. The employees table has no defaults, but department_id and manager_id allow NULL, so we can omit them:

sql
INSERT INTO employees (id, first_name, last_name, email, job_title, salary, hire_date)
VALUES (23, 'Zoya', 'Ali', 'zoya.ali@example.com', 'Intern', 20000, '2026-11-10');

SELECT id, first_name, department_id, manager_id FROM employees WHERE id = 23;
result
+----+------------+---------------+------------+
| id | first_name | department_id | manager_id |
+----+------------+---------------+------------+
| 23 | Zoya       | NULL          | NULL       |
+----+------------+---------------+------------+

Zoya has NULL in both columns. Leaving out a NOT NULL column that has no default, such as salary, would be an error.

INSERT ... SELECT: copy rows from a query

Instead of VALUES you can give a SELECT, and every row it returns is inserted. Suppose a new project, Help Centre Refresh, will involve everyone in Support (department 5) for 20 hours each. First the project, because assignments has a foreign key to projects:

sql
INSERT INTO projects (id, name, department_id, budget, start_date, end_date)
VALUES (6, 'Help Centre Refresh', 5, 90000, '2026-12-01', NULL);

SELECT COUNT(*) AS assignments_before FROM assignments WHERE project_id = 6;

INSERT INTO assignments (employee_id, project_id, hours)
SELECT id, 6, 20
FROM employees
WHERE department_id = 5;

SELECT employee_id, project_id, hours FROM assignments WHERE project_id = 6 ORDER BY employee_id;
result
+--------------------+
| assignments_before |
+--------------------+
|                  0 |
+--------------------+
+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
|          15 |          6 |    20 |
|          16 |          6 |    20 |
|          17 |          6 |    20 |
+-------------+------------+-------+

The constant 6 and 20 in the select list become the same value on every inserted row, while id comes from each employee. The column count and order of the SELECT must match the column list of the INSERT.

When a constraint says no

Constraints protect the data, and an INSERT that breaks one fails entirely: nothing is added. Here are three typical errors. The wording below is SQLite's; other databases phrase them differently but mean the same thing.

A duplicate primary key:

sql
INSERT INTO departments (id, name, city) VALUES (1, 'Operations', 'Delhi');
result
Error near line 1: UNIQUE constraint failed: departments.id

A value that does not exist in the parent table (a foreign key violation):

sql
INSERT INTO employees (id, first_name, last_name, email, department_id, job_title, salary, hire_date)
VALUES (24, 'Test', 'User', 'test.user@example.com', 99, 'Tester', 40000, '2026-11-11');
result
Error near line 1: FOREIGN KEY constraint failed

A missing value for a NOT NULL column (here no salary):

sql
INSERT INTO employees (id, first_name, last_name, email, job_title, hire_date)
VALUES (24, 'Test', 'User', 'test.user@example.com', 'Tester', '2026-11-11');
result
Error near line 1: NOT NULL constraint failed: employees.salary

After these failures, confirm nothing was added:

sql
SELECT COUNT(*) AS employees_now FROM employees;
result
+---------------+
| employees_now |
+---------------+
|            23 |
+---------------+

The count is 23: the 20 original employees plus Nikhil, Leela and Zoya. The three failed statements added nothing.

Differences between databases

  • Auto-increment ids. Real tables usually generate the id for you, and you leave id out of the INSERT. The syntax differs: MySQL uses AUTO_INCREMENT, PostgreSQL uses GENERATED ... AS IDENTITY (or the older SERIAL), SQL Server uses IDENTITY, and SQLite fills in an INTEGER PRIMARY KEY automatically. This lesson supplies ids explicitly so the examples work everywhere. See the MySQL INSERT lesson for MySQL specifics.
  • Foreign keys in SQLite are only checked when the connection has switched them on with PRAGMA foreign_keys = ON; the sample database does that.
  • Multi-row VALUES is supported by all four databases (SQL Server 2008 and newer).

Common mistakes

  • Omitting the column list. It works until the table changes, then quietly puts values in the wrong columns.
  • Putting quotes around numbers or forgetting them around text. 'Legal' is text, 7 is a number, and '7' is text that some databases will convert for you and some will not.
  • Inserting before the parent row exists. Insert the department before the employee that belongs to it.
  • Reusing a primary key. Let the database generate ids, or pick one that is free.
  • Assuming a failed multi-row INSERT added some rows. It adds all or none.

Exercise

Continuing from the steps above, add a project called Data Warehouse with id 7, belonging to department 4, budget 250000, starting on 2026-12-15 with no end date. Then assign employee 13 for 30 hours and employee 14 for 100 hours. Show the new assignments with the project name.

Show one possible answer
sql
INSERT INTO projects (id, name, department_id, budget, start_date, end_date)
VALUES (7, 'Data Warehouse', 4, 250000, '2026-12-15', NULL);

INSERT INTO assignments (employee_id, project_id, hours)
VALUES (13, 7, 30), (14, 7, 100);

SELECT p.name, a.employee_id, a.hours
FROM assignments a
JOIN projects p ON p.id = a.project_id
WHERE p.id = 7
ORDER BY a.employee_id;
result
+----------------+-------------+-------+
|      name      | employee_id | hours |
+----------------+-------------+-------+
| Data Warehouse |          13 |    30 |
| Data Warehouse |          14 |   100 |
+----------------+-------------+-------+

Key points

  • INSERT INTO table (columns) VALUES (values) adds a row; always list the columns so the statement keeps working if the table changes.
  • One INSERT can add many rows by repeating the parenthesised value lists separated by commas.
  • Columns you leave out get their default value, or NULL; a NOT NULL column with no default makes the INSERT fail.
  • INSERT ... SELECT copies rows produced by a query into a table.
  • Constraints (primary key, UNIQUE, NOT NULL, foreign key) reject bad rows with an error and the table is left unchanged.

Check yourself

1. Why should you list the column names in an INSERT?
2. What happens to a column you leave out of an INSERT?
3. Which statement copies the rows of a query into a table?
4. You insert a row with department_id 99, which does not exist in departments. What should happen?