Chapter 05 of 35

INSERT INTO

On this page

In the previous chapter, CREATE DATABASE & CREATE TABLE, we built the four company_db tables and a practice table called projects. They have structure but no data. The statement that adds rows to a table is INSERT.

INSERT looks simple, and the basic form is. But in real applications you also need to know what happens to the columns you leave out, how auto-generated ids work, why a single bad row can reject a whole batch, and what to do when the row you are inserting already exists. That is what this chapter covers, with the real output of every statement.


The Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
  • The column list says which columns you are filling, and in which order.

  • The VALUES list gives one value per column, in the same order.

  • Text and dates go in single quotes ('Pune', '2024-04-01'); numbers do not.

MySQL replies with how many rows were affected:

Query OK, 1 row affected (0.00 sec)

INSERT is a DML (Data Manipulation Language) command: it changes the rows, not the structure.


Filling company_db

The data part of the company_db script from Setting Up MySQL is just four INSERT statements, one per table. Here is the first one:

INSERT INTO departments VALUES
 (10, 'Engineering', 'Bengaluru'),
 (20, 'Sales',       'Mumbai'),
 (30, 'HR',          'Delhi'),
 (40, 'Marketing',   'Pune'),
 (50, 'Research',    'Hyderabad');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Two things to notice:

  • There is no column list. That is allowed when you supply a value for every column, in the order the columns were defined in CREATE TABLE.

  • One statement inserts five rows: each row is a bracketed group, separated by commas. For a multi-row insert MySQL also prints a Records / Duplicates / Warnings summary.

Now copy the remaining three INSERT statements (for employees, customers and orders) from the script and run them in that order. You should see 10, 5 and 8 rows affected. Check the totals:

SELECT (SELECT COUNT(*) FROM departments) AS departments,
       (SELECT COUNT(*) FROM employees)   AS employees,
       (SELECT COUNT(*) FROM customers)   AS customers,
       (SELECT COUNT(*) FROM orders)      AS orders;
+-------------+-----------+-----------+--------+
| departments | employees | customers | orders |
+-------------+-----------+-----------+--------+
|           5 |        10 |         5 |      8 |
+-------------+-----------+-----------+--------+

(The small SELECTs inside brackets are subqueries. You will learn them in Subqueries; here they just count each table.)

Why the Order of Inserts Matters

Suppose you had run the employees insert before the departments insert. Even a single employee fails:

INSERT INTO employees VALUES
 (2, 'Priya Iyer', 'priya@company.com', 10, 1, 95000.00, '2019-07-01', 'Chennai');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))

The foreign key says "dept_id must exist in departments", and department 10 does not exist yet. So parent rows must be inserted before child rows — the same order in which we created the tables.

The same applies inside the employees insert: Aarav (emp_id 1) is the manager of almost everyone, so his row is listed first. InnoDB checks foreign keys row by row as it inserts, so if Priya's row (manager 1) came before Aarav's row in the same statement, it would fail with the same error.


Inserting With a Column List

Writing the column list is the recommended habit, even when you fill every column. Let us switch to our practice table projects, which has an AUTO_INCREMENT id and two defaults:

CREATE TABLE projects (
  project_id INT AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(100) NOT NULL UNIQUE,
  dept_id    INT,
  budget     DECIMAL(12,2) NOT NULL DEFAULT 0,
  status     VARCHAR(20) NOT NULL DEFAULT 'Planned',
  start_date DATE,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

(If you created it in the previous chapter, it is already there.) First, an insert with no column list, giving every value:

INSERT INTO projects VALUES (1, 'Payments Revamp', 10, 500000.00, 'Active', '2024-04-01');

Then an insert that names only three columns:

INSERT INTO projects (title, dept_id, budget)
VALUES ('Campus Hiring Drive', 30, 120000.00);

SELECT * FROM projects;
+------------+---------------------+---------+-----------+---------+------------+
| project_id | title               | dept_id | budget    | status  | start_date |
+------------+---------------------+---------+-----------+---------+------------+
|          1 | Payments Revamp     |      10 | 500000.00 | Active  | 2024-04-01 |
|          2 | Campus Hiring Drive |      30 | 120000.00 | Planned | NULL       |
+------------+---------------------+---------+-----------+---------+------------+

Look at row 2 and the columns we did not mention:

  • project_id → 2, generated by AUTO_INCREMENT (one more than the highest id so far).

  • status → 'Planned', its DEFAULT.

  • start_date → NULL, because it has no default and allows NULL.

Rule for omitted columns: MySQL uses the column's DEFAULT if it has one; otherwise NULL if the column allows it; otherwise (a NOT NULL column with no default) the insert fails.

Why Always Write the Column List?

The form without a column list depends on the current column order of the table. If someone later adds a column (with ALTER TABLE), every such INSERT in your application breaks with "Column count doesn't match" — or worse, if types happen to match, puts values in the wrong columns. With a column list, your statement keeps working and says exactly what goes where. It also lets you skip id and default columns.

Getting the Generated Id: LAST_INSERT_ID()

After inserting a row with an auto-generated id, an application usually needs that id — for example, to insert the order items that belong to a new order. MySQL remembers it per connection:

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

Because the value is stored per connection, it is safe even when many users insert at the same time: you always get the id your last insert generated, never someone else's. (Do not use SELECT MAX(project_id) for this — another user's insert could sneak in between.)


Inserting Many Rows at Once

INSERT INTO projects (title, dept_id) VALUES
  ('Brand Survey',         40),
  ('Lead Scoring Model',   20),
  ('Office Wi-Fi Upgrade', NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

Notice: the three rows got ids 3, 4 and 5, but LAST_INSERT_ID() returns 3 — the id of the first row of a multi-row insert, not the last. This is a well-known MySQL detail that trips people up.

Why bother with multi-row inserts? Speed. Each statement has overhead: a network round trip, parsing, and a commit. Inserting 1,000 rows as one statement (or a few batches) is many times faster than 1,000 separate statements. This is how data import scripts and ORMs' "bulk insert" features work.


The DEFAULT Keyword and the SET Form

You can ask for a column's default explicitly with the keyword DEFAULT:

INSERT INTO projects (title, budget, dept_id, status)
VALUES ('Patent Filing', DEFAULT, 50, DEFAULT);

MySQL also supports a non-standard SET form, which puts each column next to its value. It is easy to read for single rows, but it is MySQL-only:

INSERT INTO projects SET title = 'Women''s Day Event', dept_id = 30, budget = 25000;

Notice 'Women''s Day Event': to put a single quote inside a string, write it twice. (MySQL also accepts \', but doubling is the SQL standard and works everywhere.) In application code, never build SQL by gluing strings — use parameterized queries (prepared statements) so quotes are handled for you and SQL injection is impossible.

SELECT * FROM projects;
+------------+----------------------+---------+-----------+---------+------------+
| project_id | title                | dept_id | budget    | status  | start_date |
+------------+----------------------+---------+-----------+---------+------------+
|          1 | Payments Revamp      |      10 | 500000.00 | Active  | 2024-04-01 |
|          2 | Campus Hiring Drive  |      30 | 120000.00 | Planned | NULL       |
|          3 | Brand Survey         |      40 |      0.00 | Planned | NULL       |
|          4 | Lead Scoring Model   |      20 |      0.00 | Planned | NULL       |
|          5 | Office Wi-Fi Upgrade |    NULL |      0.00 | Planned | NULL       |
|          6 | Patent Filing        |      50 |      0.00 | Planned | NULL       |
|          7 | Women's Day Event    |      30 |  25000.00 | Planned | NULL       |
+------------+----------------------+---------+-----------+---------+------------+

When INSERT Fails: The Errors You Will See

The rules we put in CREATE TABLE now pay off: MySQL rejects bad rows instead of storing them. Each failed statement below inserts nothing.

1. Foreign Key Violation

INSERT INTO projects (title, dept_id) VALUES ('Mobile App', 99);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`projects`, CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))

There is no department 99.

2. Missing a Required Column

INSERT INTO projects (dept_id) VALUES (10);
ERROR 1364 (HY000): Field 'title' doesn't have a default value

title is NOT NULL with no default, so it cannot be left out.

3. Duplicate Value in a PRIMARY KEY or UNIQUE Column

INSERT INTO projects (title, dept_id) VALUES ('Brand Survey', 20);
ERROR 1062 (23000): Duplicate entry 'Brand Survey' for key 'projects.title'

4. Wrong Number of Values

INSERT INTO projects (title, dept_id) VALUES ('Data Platform', 10, 900000);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Two columns listed, three values given.

5. Values That Don't Fit the Type

INSERT INTO projects (title, start_date) VALUES ('Quarterly Audit', '01-04-2024');
INSERT INTO projects (title, budget) VALUES ('Quarterly Audit', 'fifty thousand');
ERROR 1292 (22007): Incorrect date value: '01-04-2024' for column 'start_date' at row 1
ERROR 1366 (HY000): Incorrect decimal value: 'fifty thousand' for column 'budget' at row 1

Dates must be 'YYYY-MM-DD', and a DECIMAL column needs a number (see SQL Data Types).

6. One Bad Row Rejects the Whole Statement

What happens if just one row in a multi-row insert is bad?

INSERT INTO projects (title, dept_id) VALUES
  ('Data Platform', 10),
  (NULL,            20),
  ('Chatbot Pilot', 10);
ERROR 1048 (23000): Column 'title' cannot be null
SELECT COUNT(*) FROM projects;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

Still 7 rows. Even 'Data Platform', which came before the bad row and was perfectly valid, was not kept. With InnoDB and strict mode, a single statement is atomic: either all its rows are inserted or none are. This is a very useful guarantee — you never end up with half of a batch. (To make several statements all-or-nothing, you need a transaction; see Transactions in SQL.)

Side Effect: Gaps in AUTO_INCREMENT

Now insert the valid row on its own:

INSERT INTO projects (title, dept_id) VALUES ('Data Platform', 10);
SELECT project_id, title FROM projects;
+------------+----------------------+
| project_id | title                |
+------------+----------------------+
|          3 | Brand Survey         |
|          2 | Campus Hiring Drive  |
|         13 | Data Platform        |
|          4 | Lead Scoring Model   |
|          5 | Office Wi-Fi Upgrade |
|          6 | Patent Filing        |
|          1 | Payments Revamp      |
|          7 | Women's Day Event    |
+------------+----------------------+

Two surprises here:

  • The new id is 13, not 8. Ids 8 to 12 were handed out to the failed inserts above, and InnoDB never takes them back. Generated ids are guaranteed to be unique and increasing, not gap-free. Gaps also appear after deletes and rolled-back transactions. Never use an auto-increment id as a "count of rows" or assume ids are consecutive (for invoice numbers that must be gap-free, a different design is needed).

  • The rows came back sorted by title, not by id. We did not ask for any order, so MySQL returned rows in whatever order was cheapest — here it read the title index, which is sorted by title. A table has no built-in order; if order matters, say so with ORDER BY (see ORDER BY & LIMIT).


When the Row Already Exists

A very common real-world situation: you want to insert a row, but a row with the same key might already be there (re-running an import, a user clicking "Save" twice). MySQL gives three options.

INSERT IGNORE: Skip Duplicates

INSERT IGNORE INTO projects (title, dept_id) VALUES
  ('Brand Survey',  20),
  ('Chatbot Pilot', 10);
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1
SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'Brand Survey' for key 'projects.title' |
+---------+------+---------------------------------------------------------+

The duplicate row was skipped with a warning, and the other row was inserted. Use it with care: IGNORE turns many errors into warnings, not just duplicates, so bad data can slip through quietly.

INSERT ... ON DUPLICATE KEY UPDATE: Insert or Update ("Upsert")

Often you want: "insert this row, or if it already exists, update it". This is called an upsert.

INSERT INTO projects (title, dept_id, budget)
VALUES ('Brand Survey', 40, 75000.00) AS new
ON DUPLICATE KEY UPDATE budget = new.budget;
Query OK, 2 rows affected (0.01 sec)
SELECT project_id, title, dept_id, budget FROM projects WHERE title = 'Brand Survey';
+------------+--------------+---------+----------+
| project_id | title        | dept_id | budget   |
+------------+--------------+---------+----------+
|          3 | Brand Survey |      40 | 75000.00 |
+------------+--------------+---------+----------+

'Brand Survey' already existed, so instead of failing, MySQL updated the existing row's budget. The row kept its id (3). Points to understand:

  • AS new gives the row we tried to insert a name, so the UPDATE part can refer to its values as new.budget. This row-alias syntax exists since MySQL 8.0.19. Older code writes VALUES(budget) instead, which still works but is deprecated.

  • The "rows affected" count tells you what happened: 1 = a new row was inserted, 2 = an existing row was updated, 0 = the row existed and already had these values. Running the exact same statement a second time gives:

Query OK, 0 rows affected (0.00 sec)

REPLACE: Delete and Re-insert

REPLACE looks like an upsert, but works differently: if a row with the same key exists, MySQL deletes it and then inserts the new row.

REPLACE INTO projects (project_id, title, dept_id)
VALUES (2, 'Campus Hiring Drive', 30);
Query OK, 2 rows affected (0.00 sec)
SELECT * FROM projects WHERE project_id = 2;
+------------+---------------------+---------+--------+---------+------------+
| project_id | title               | dept_id | budget | status  | start_date |
+------------+---------------------+---------+--------+---------+------------+
|          2 | Campus Hiring Drive |      30 |   0.00 | Planned | NULL       |
+------------+---------------------+---------+--------+---------+------------+

The "2 rows affected" is 1 delete + 1 insert. And look at the budget: it was 120000.00, and now it is 0.00. Because the old row was deleted, every column we did not supply went back to its default. This is the classic REPLACE trap. It also fires delete triggers and fails (or cascades) if other tables reference the row through foreign keys. In most cases, ON DUPLICATE KEY UPDATE is what you actually want.

Statement

If the key already exists

Typical use

INSERT

Error 1062, nothing inserted

Normal inserts

INSERT IGNORE

Row skipped with a warning

Re-runnable imports where existing rows should stay as they are

INSERT ... ON DUPLICATE KEY UPDATE

Existing row is updated (same id)

Upserts: counters, "save" buttons, syncing data

REPLACE

Existing row is deleted, new row inserted; unspecified columns reset

Rarely; only when a full overwrite is really intended

All three are MySQL extensions. The SQL standard way to upsert is the MERGE statement (supported by Oracle, SQL Server and PostgreSQL 15+; PostgreSQL also has its own INSERT ... ON CONFLICT).


Copying Rows From Another Table: INSERT ... SELECT

Instead of VALUES, an INSERT can take its rows from a SELECT. This is how you copy, archive or summarise data inside the database without sending it to your application and back.

CREATE TABLE high_earners (
  emp_id INT PRIMARY KEY,
  name   VARCHAR(50) NOT NULL,
  salary DECIMAL(10,2) NOT NULL
);

INSERT INTO high_earners (emp_id, name, salary)
SELECT emp_id, name, salary
FROM employees
WHERE salary >= 90000;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
SELECT * FROM high_earners;
+--------+--------------+-----------+
| emp_id | name         | salary    |
+--------+--------------+-----------+
|      1 | Aarav Sharma | 150000.00 |
|      2 | Priya Iyer   |  95000.00 |
|      3 | Rohan Mehta  |  95000.00 |
|      4 | Sneha Kapoor | 110000.00 |
+--------+--------------+-----------+

The SELECT must return the same number of columns as the column list, in the same order; the column names do not need to match. (The WHERE clause gets its own chapter, WHERE Clause.)

CREATE TABLE ... AS SELECT

You can also create a new table and fill it in one step:

CREATE TABLE eng_team AS
SELECT emp_id, name, salary FROM employees WHERE dept_id = 10;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
DESCRIBE eng_team;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| emp_id | int           | NO   |     | NULL    |       |
| name   | varchar(50)   | NO   |     | NULL    |       |
| salary | decimal(10,2) | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

The rows and the column types (including NOT NULL) were copied, but the Key column is empty: emp_id is no longer a primary key. CREATE TABLE ... AS SELECT does not copy primary keys, indexes or foreign keys. Compare this with CREATE TABLE ... LIKE from the previous chapter, which copies the structure and indexes but no rows:

Copies

CREATE TABLE t2 LIKE t1

CREATE TABLE t2 AS SELECT * FROM t1

Columns and types

Yes

Yes

Rows

No

Yes

Primary key and indexes

Yes

No

Foreign keys

No

No

For a full copy with keys and data, combine them: CREATE TABLE t2 LIKE t1; then INSERT INTO t2 SELECT * FROM t1;.


Cleaning Up

The practice tables are not part of company_db, and later chapters assume only the four original tables exist. Remove them:

DROP TABLE projects, high_earners, eng_team;

We never changed the four company_db tables in this chapter, so they still hold exactly the sample data. If you ever experiment on them, just re-run the company_db.sql script to start fresh.


Common Mistakes

  • Leaving out the column list – the statement silently depends on column order and breaks when the table changes.

  • Inserting child rows before parent rows – foreign key error 1452.

  • Quoting numbers or not quoting text and dates – VALUES (Pune) is read as a column name; '2024-04-01' must be quoted.

  • Using DD-MM-YYYY dates – MySQL wants 'YYYY-MM-DD'.

  • Using MAX(id) to find the id you just inserted – use LAST_INSERT_ID(), which is per connection (and remember it returns the first id of a multi-row insert).

  • Expecting auto-increment ids without gaps – failed inserts, deletes and rollbacks leave gaps.

  • Using REPLACE as an upsert – it deletes the old row, resetting unspecified columns.

  • Inserting rows one statement at a time in a loop – batch them into multi-row inserts.


Interview Questions

What happens to columns not mentioned in an INSERT?

They get their DEFAULT value; if there is none, NULL (if allowed). An AUTO_INCREMENT column gets the next generated value. If a NOT NULL column has no default, the insert fails (error 1364 in strict mode).

How do you insert multiple rows in one statement?

List several value groups separated by commas: INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y'), (3, 'z');. It is much faster than separate statements, and the statement is atomic.

How do you get the auto-generated id of the row you just inserted?

SELECT LAST_INSERT_ID(); It is maintained per connection, so concurrent inserts by other sessions do not affect it. For a multi-row insert it returns the id of the first inserted row.

What is the difference between INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE?

On a duplicate key, INSERT IGNORE skips the row with a warning; REPLACE deletes the existing row and inserts the new one (unspecified columns reset to defaults); ON DUPLICATE KEY UPDATE updates the chosen columns of the existing row in place.

How do you copy data from one table to another?

INSERT INTO target (cols) SELECT cols FROM source WHERE ...; or create and fill a new table with CREATE TABLE target AS SELECT ...; (which does not copy keys or indexes).

Why can auto-increment values have gaps?

InnoDB allocates values before the insert succeeds and does not reuse them. Failed inserts, rolled-back transactions and deleted rows leave gaps, and INSERT IGNORE or upserts can too. Auto-increment guarantees uniqueness, not continuity.

If one row in a multi-row INSERT violates a constraint, what happens?

With InnoDB in strict mode, the whole statement fails and no rows from it are inserted, because each statement is atomic.


The Main Idea

INSERT INTO t (c1, c2) VALUES (v1, v2);              ← always list the columns
INSERT INTO t (c1, c2) VALUES (..), (..), (..);      ← many rows, one atomic statement
Omitted column  → DEFAULT, else NULL, else error 1364
Generated id    → SELECT LAST_INSERT_ID();  (per connection; first id of a batch)
Order           → parent rows before child rows (FK error 1452)
Duplicates      → INSERT IGNORE (skip) | ON DUPLICATE KEY UPDATE (upsert) | REPLACE (delete+insert)
Copy rows       → INSERT INTO t2 (...) SELECT ... FROM t1;
                  CREATE TABLE t2 AS SELECT ...   (no keys copied)
Auto-increment  → unique and increasing, NOT gap-free

Our tables are now full of data. Next, in SELECT Statement, we start the most important part of SQL: reading that data back in exactly the shape we need.