SQL Tutorial › Modifying Data › INSERT

SQL INSERT INTO Statement

Q: How do I insert multiple rows in SQL?

List several value groups after VALUES, separated by commas: INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');

Q: How do I insert data from one table into another?

Use INSERT INTO target (columns) SELECT columns FROM source WHERE ...; The selected columns must match the target columns in number and order.

Q: How do I get the ID of the row I just inserted?

For an AUTO_INCREMENT column, call SELECT LAST_INSERT_ID(); right after the insert, in the same connection. See [AUTO_INCREMENT](lesson:autoinc).

Q: How do I insert a row only if it does not exist?

Use INSERT IGNORE, or INSERT ... ON DUPLICATE KEY UPDATE to update the existing row instead. See [Upsert](lesson:upsert).

BeginnerLesson 18 of 6911 min read10 queries run on MySQL 8.4

INSERT INTO adds new rows to a table. You can insert one row, many rows in one statement, or the result of a query. This lesson inserts into the sample customers and products tables and shows exactly what MySQL reports, including the errors you get when a row breaks a rule.

In this lesson

Insert one row and many rows
Name the columns so inserts survive table changes
Rely on defaults and NULLs for missing columns
Copy rows with INSERT INTO ... SELECT

INSERT syntax

MySQL

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

The values are matched to the columns by position: the first value goes into the first listed column, and so on.

Sample data

customers · 7 rows

customer_id	customer_name	email	city	joined_on
1	Ananya Sharma	ananya@example.com	Mumbai	2024-01-15
2	Rohit Kumar	rohit.k@example.com	Delhi	2024-02-03
3	Fatima Khan	fatima@example.com	Hyderabad	2024-03-21
4	David Fernandes	NULL	Goa	2024-05-09
5	Ishita Bose	ishita@example.com	Kolkata	2024-07-30
6	Manoj Pillai	manoj.p@example.com	Chennai	2025-01-11
7	Sara Thomas	sara.t@example.com	Bengaluru	2025-03-02

Insert one row

SQL

INSERT INTO customers (customer_id, customer_name, email, city, joined_on)VALUES (8, 'Kiran Rao', 'kiran.rao@example.com', 'Bengaluru', '2025-06-15'); SELECT * FROM customers WHERE customer_id = 8;

ResultMySQL 8.4

mysql> INSERT INTO customers (customer_id, customer_name, email, city, joined…

Query OK, 1 row affected

mysql> SELECT * FROM customers WHERE customer_id = 8

customer_id	customer_name	email	city	joined_on
8	Kiran Rao	kiran.rao@example.com	Bengaluru	2025-06-15

1 row in set

How this query works, step by step

The column list names the five columns that receive values.
VALUES (...) supplies one value per column, in the same order. Text and dates are quoted; numbers are not.
MySQL reports Query OK, 1 row affected.
The SELECT reads the new row back.

Insert several rows at once

Separate the row groups with commas. One multi-row INSERT is much faster than many single-row statements:

SQL

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (10, 'Webcam HD', 'Electronics', 2499, 40),       (11, 'Sticky Notes', 'Stationery', 99, 500),       (12, 'Footrest', 'Furniture', 1299, 25); SELECT product_id, product_name, price FROM products WHERE product_id >= 10;

ResultMySQL 8.4

mysql> INSERT INTO products (product_id, product_name, category, price, stock)

Query OK, 3 rows affected

mysql> SELECT product_id, product_name, price FROM products WHERE product_id …

product_id	product_name	price
10	Webcam HD	2499
11	Sticky Notes	99
12	Footrest	1299

3 rows in set

How this query works, step by step

Each parenthesised group is one row: three rows here.
MySQL inserts them in one statement and reports 3 rows affected.

Insert only some columns

Columns you leave out get their default value, or NULL if they have no default:

SQL

INSERT INTO customers (customer_id, customer_name, city)VALUES (9, 'Leela Nair', 'Kochi'); SELECT * FROM customers WHERE customer_id = 9;

ResultMySQL 8.4

mysql> INSERT INTO customers (customer_id, customer_name, city)

Query OK, 1 row affected

mysql> SELECT * FROM customers WHERE customer_id = 9

customer_id	customer_name	email	city	joined_on
9	Leela Nair	NULL	Kochi	NULL

1 row in set

How this query works, step by step

Only three columns are named.
email and joined_on are not given and have no default, so they are stored as NULL.

Insert without a column list

You may leave out the column list, but then you must give a value for every column in table order:

SQL

INSERT INTO departments VALUES (6, 'Legal', 'Delhi');SELECT * FROM departments;

ResultMySQL 8.4

mysql> INSERT INTO departments VALUES (6, 'Legal', 'Delhi')

Query OK, 1 row affected

mysql> SELECT * FROM departments

dept_id	dept_name	location
1	Engineering	Bengaluru
2	Sales	Mumbai
3	Marketing	Pune
4	HR	Delhi
5	Finance	Mumbai
6	Legal	Delhi

6 rows in set

Watch out

Avoid this form in application code. If someone adds a column to the table later, every insert without a column list breaks, or worse, puts values into the wrong columns.

Errors you will meet

A duplicate primary key:

MySQL

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Duplicate Person');

ResultMySQL 8.4

ERROR 1062 (23000): Duplicate entry '1' for key 'customers.PRIMARY'

A required column left empty (customer_name is NOT NULL):

MySQL

INSERT INTO customers (customer_id, city) VALUES (10, 'Pune');

ResultMySQL 8.4

ERROR 1364 (HY000): Field 'customer_name' doesn't have a default value

A value count that does not match the column count:

MySQL

INSERT INTO departments (dept_id, dept_name) VALUES (7, 'Support', 'Pune');

ResultMySQL 8.4

ERROR 1136 (21S01): Column count doesn't match value count at row 1

A foreign key that points nowhere (there is no customer 99):

MySQL

INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (1011, 99, '2025-06-10', 'Pending');

ResultMySQL 8.4

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

How this query works, step by step

Error 1062: the primary key value already exists.
Error 1364: a NOT NULL column without a default was not given a value.
Error 1136: three values for two columns.
Error 1452: the foreign key on customer_id refuses an order for a customer that does not exist.

Note

An INSERT that fails inserts nothing. In a multi-row insert, one bad row makes the whole statement fail (for InnoDB tables).

INSERT INTO … SELECT

Insert the result of a query instead of literal values. Archive all cancelled orders into a new table:

SQL

CREATE TABLE cancelled_orders (  order_id    INT PRIMARY KEY,  customer_id INT,  order_date  DATE); INSERT INTO cancelled_orders (order_id, customer_id, order_date)SELECT order_id, customer_id, order_dateFROM ordersWHERE status = 'Cancelled'; SELECT * FROM cancelled_orders;

ResultMySQL 8.4

mysql> CREATE TABLE cancelled_orders (

Query OK, 0 rows affected

mysql> INSERT INTO cancelled_orders (order_id, customer_id, order_date)

Query OK, 1 row affected

mysql> SELECT * FROM cancelled_orders

order_id	customer_id	order_date
1004	3	2025-02-14

1 row in set

How this query works, step by step

CREATE TABLE makes an empty archive table.
The SELECT finds the cancelled orders (one row).
INSERT INTO ... SELECT writes every row the query returns into the archive. There is no VALUES keyword.

Insert calculated values

Values can be expressions and functions:

SQL

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (13, UPPER('laptop stand'), 'Furniture', 1500 + 499, 10 * 2); SELECT * FROM products WHERE product_id = 13;

ResultMySQL 8.4

mysql> INSERT INTO products (product_id, product_name, category, price, stock)

Query OK, 1 row affected

mysql> SELECT * FROM products WHERE product_id = 13

product_id	product_name	category	price	stock
13	LAPTOP STAND	Furniture	1999	20

1 row in set

Exercise: Add a department

Insert a new department with dept_id 6, name 'Support' and location 'Hyderabad', then show all departments.

Expected resultMySQL 8.4

mysql> INSERT INTO departments (dept_id, dept_name, location)

Query OK, 1 row affected

mysql> SELECT * FROM departments

dept_id	dept_name	location
1	Engineering	Bengaluru
2	Sales	Mumbai
3	Marketing	Pune
4	HR	Delhi
5	Finance	Mumbai
6	Support	Hyderabad

6 rows in set

Hint 1

Name the three columns, then give three values.

SQL

INSERT INTO departments (dept_id, dept_name, location)VALUES (6, 'Support', 'Hyderabad'); SELECT * FROM departments;

ResultMySQL 8.4

mysql> INSERT INTO departments (dept_id, dept_name, location)

Query OK, 1 row affected

mysql> SELECT * FROM departments

dept_id	dept_name	location
1	Engineering	Bengaluru
2	Sales	Mumbai
3	Marketing	Pune
4	HR	Delhi
5	Finance	Mumbai
6	Support	Hyderabad

6 rows in set

Exercise: Two new customers

Insert customers 8 and 9 in one statement: 'Om Prakash' from 'Jaipur' and 'Rhea Das' from 'Kolkata'. Leave the email and date empty.

Expected resultMySQL 8.4

mysql> INSERT INTO customers (customer_id, customer_name, city)

Query OK, 2 rows affected

mysql> SELECT customer_id, customer_name, city, email FROM customers WHERE cu…

customer_id	customer_name	city	email
8	Om Prakash	Jaipur	NULL
9	Rhea Das	Kolkata	NULL

2 rows in set

Hint 1

One INSERT with two row groups separated by a comma.

SQL

INSERT INTO customers (customer_id, customer_name, city)VALUES (8, 'Om Prakash', 'Jaipur'),       (9, 'Rhea Das', 'Kolkata'); SELECT customer_id, customer_name, city, email FROM customers WHERE customer_id >= 8;

ResultMySQL 8.4

mysql> INSERT INTO customers (customer_id, customer_name, city)

Query OK, 2 rows affected

mysql> SELECT customer_id, customer_name, city, email FROM customers WHERE cu…

customer_id	customer_name	city	email
8	Om Prakash	Jaipur	NULL
9	Rhea Das	Kolkata	NULL

2 rows in set

Quick quiz

1. How are values matched to columns in INSERT INTO t (a, b) VALUES (1, 2)?

2. A column is left out of the INSERT and has no DEFAULT. What is stored?

3. Which statement copies rows from one table into another existing table?

INSERT INTO target (cols) SELECT ... FROM source copies query results.

4. Why is a multi-row INSERT preferred to many single-row INSERTs?

INSERT FAQ

How do I insert multiple rows in SQL?

List several value groups after VALUES, separated by commas: INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');

How do I insert data from one table into another?

Use INSERT INTO target (columns) SELECT columns FROM source WHERE ...; The selected columns must match the target columns in number and order.

How do I get the ID of the row I just inserted?

For an AUTO_INCREMENT column, call SELECT LAST_INSERT_ID(); right after the insert, in the same connection. See AUTO_INCREMENT.

How do I insert a row only if it does not exist?

Use INSERT IGNORE, or INSERT ... ON DUPLICATE KEY UPDATE to update the existing row instead. See Upsert.