MySQL INSERT Statement

Last updated: October 19, 2026Oct 19, 2026 7 min read

The INSERT statement adds new rows to a table. It is the "C" (Create) in CRUD. This tutorial covers every common form: one row, many rows, rows copied from a query, and the MySQL options for handling duplicates. It also shows how to get the generated ID and how to insert efficiently from Java.

1. Syntax

syntax.sql

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

Figure 1: the forms of INSERT, and the four ways to handle duplicate keys.

2. Inserting One Row

one-row.sql

INSERT INTO authors (name, country) VALUES ('R. K. Narayan', 'India');
SELECT * FROM authors;

Output

Query OK, 1 row affected

Result: 1 row

author_id	name	country
1	R. K. Narayan	India

Always list the columns. INSERT INTO authors VALUES (…) without a list depends on column order and breaks when someone adds a column.

3. Leaving Out Columns

Columns you leave out get their DEFAULT, AUTO_INCREMENT number or NULL:

defaults.sql

INSERT INTO authors (name) VALUES ('Ruskin Bond');
SELECT * FROM authors;

Result: 2 rows

author_id	name	country
1	R. K. Narayan	India
2	Ruskin Bond	India

4. Inserting Multiple Rows

One statement with several value lists is much faster than many single-row inserts:

multi-row.sql

INSERT INTO authors (name, country) VALUES
  ('Chimamanda Ngozi Adichie', 'Nigeria'),
  ('Haruki Murakami', 'Japan'),
  ('Kiran Desai', 'India');

INSERT INTO books (isbn, title, author_id, price, stock) VALUES
  ('9780143039648', 'Malgudi Days',          1, 299.00, 40),
  ('9780143333661', 'The Room on the Roof',  2, 199.00, 25),
  ('9780007200283', 'Half of a Yellow Sun',  3, 450.00, 12),
  ('9780099448822', 'Norwegian Wood',        4, 499.00, 18),
  ('9780802142818', 'The Inheritance of Loss', 5, 399.00, 9);

Output

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

If any row fails, the whole statement fails and no rows are inserted (InnoDB statements are atomic).

5. INSERT … SET

MySQL also accepts a SET form, handy when there are many columns:

set-form.sql

INSERT INTO books SET isbn = '9780143414926', title = 'Swami and Friends', author_id = 1, price = 250;
SELECT book_id, title, stock, added_on IS NOT NULL AS dated FROM books WHERE author_id = 1;

Result: 2 rows

book_id	title	stock	dated
1	Malgudi Days	40	1
6	Swami and Friends	0	1

6. Getting the Generated ID

LAST_INSERT_ID() returns the AUTO_INCREMENT value created by your session's last insert; other sessions don't affect it:

last-id.sql

INSERT INTO authors (name, country) VALUES ('Arundhati Roy', 'India');
SELECT LAST_INSERT_ID() AS new_author_id;

Result: 1 row

new_author_id
6

Use it to insert related rows:

use-id.sql

INSERT INTO books (isbn, title, author_id, price, stock)
VALUES ('9780006550686', 'The God of Small Things', LAST_INSERT_ID(), 350, 30);
SELECT b.title, a.name FROM books b JOIN authors a USING (author_id) WHERE a.name = 'Arundhati Roy';

Result: 1 row

title	name
The God of Small Things	Arundhati Roy

After a multi-row insert, LAST_INSERT_ID() returns the ID of the first row inserted.

7. INSERT … SELECT

Copy rows produced by a query, for example a low-stock re-order list:

insert-select.sql

CREATE TABLE reorder_list (book_id INT PRIMARY KEY, title VARCHAR(80), need INT);
INSERT INTO reorder_list (book_id, title, need)
SELECT book_id, title, 20 - stock FROM books WHERE stock < 20;
SELECT * FROM reorder_list;

Result: 4 rows

book_id	title	need
3	Half of a Yellow Sun	8
4	Norwegian Wood	2
5	The Inheritance of Loss	11
6	Swami and Friends	20

8. Common Insert Errors

errors.sql

INSERT INTO books (isbn, title, author_id, price) VALUES ('9780143039648', 'Duplicate ISBN', 1, 100);
INSERT INTO books (isbn, title, author_id, price) VALUES ('9781111111111', 'Ghost Author', 99, 100);
INSERT INTO books (isbn, title, author_id) VALUES ('9782222222222', 'No Price', 1);
INSERT INTO books (isbn, title, author_id, price) VALUES ('9783333333333', 'Too Pricey', 1, 123456.00);
INSERT INTO authors (name, country) VALUES ('Only one value');

Output

ERROR 1062 (23000): Duplicate entry '9780143039648' for key 'books.isbn'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookstore`.`books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`author_id`))
ERROR 1364 (HY000): Field 'price' doesn't have a default value
ERROR 1264 (22003): Out of range value for column 'price' at row 1
ERROR 1136 (21S01): Column count doesn't match value count at row 1

9. Handling Duplicates

INSERT IGNORE skips rows that would break a unique key (and some other errors) and turns the error into a warning:

insert-ignore.sql

INSERT IGNORE INTO books (isbn, title, author_id, price) VALUES
  ('9780143039648', 'Malgudi Days (dup)', 1, 299),
  ('9780670921607', 'A Tiger for Malgudi', 1, 320);
SHOW WARNINGS;

Output

Query OK, 1 row affected, 1 warning
Records: 2  Duplicates: 1  Warnings: 1

Result: 1 row

Level	Code	Message
Warning	1062	Duplicate entry '9780143039648' for key 'books.isbn'

ON DUPLICATE KEY UPDATE updates the existing row instead. Here a delivery adds stock, inserting the book if it's new:

upsert.sql

INSERT INTO books (isbn, title, author_id, price, stock)
VALUES ('9780099448822', 'Norwegian Wood', 4, 499, 10) AS new
ON DUPLICATE KEY UPDATE stock = books.stock + new.stock;

SELECT title, stock FROM books WHERE isbn = '9780099448822';

Output

Query OK, 2 rows affected

Result: 1 row

title	stock
Norwegian Wood	28

The AS new alias (MySQL 8.0.19+) lets you refer to the incoming values; the older VALUES(stock) function still works but is deprecated. A row count of 2 means "updated an existing row", 1 means "inserted".

REPLACE deletes the old row and inserts the new one. Columns you don't supply are reset to defaults, and the delete fires delete triggers and cascades, so prefer ON DUPLICATE KEY UPDATE:

replace.sql

REPLACE INTO books (isbn, title, author_id, price) VALUES ('9780099448822', 'Norwegian Wood', 4, 520);
SELECT title, price, stock FROM books WHERE isbn = '9780099448822';

Output

Query OK, 2 rows affected

Result: 1 row

title	price	stock
Norwegian Wood	520.00	0

10. Inserting from Java

Use a PreparedStatement with placeholders (never string concatenation), and batch many rows into one round trip. rewriteBatchedStatements=true lets the driver send them as one multi-row INSERT:

BookImporter.java

import java.math.BigDecimal;
import java.sql.*;

public class BookImporter {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/bookstore?rewriteBatchedStatements=true";
        String sql = "INSERT INTO books (isbn, title, author_id, price, stock) VALUES (?, ?, ?, ?, ?)";

        try (Connection con = DriverManager.getConnection(url, "app_user", "your-password");
             PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            con.setAutoCommit(false);
            Object[][] rows = {
                {"9780143416982", "The Guide", 1, "299.00", 15},
                {"9780143418726", "The English Teacher", 1, "275.00", 10}
            };
            for (Object[] r : rows) {
                ps.setString(1, (String) r[0]);
                ps.setString(2, (String) r[1]);
                ps.setInt(3, (Integer) r[2]);
                ps.setBigDecimal(4, new BigDecimal((String) r[3]));
                ps.setInt(5, (Integer) r[4]);
                ps.addBatch();
            }
            ps.executeBatch();
            con.commit();

            try (ResultSet keys = ps.getGeneratedKeys()) {
                while (keys.next()) System.out.println("New book_id: " + keys.getLong(1));
            }
        }
    }
}

11. Practice Exercises

Exercise 1: Add the author 'Amitav Ghosh' (India) and his book 'The Hungry Tide' (ISBN 9780007141784, price 399, stock 14) using LAST_INSERT_ID().

solution.sql

INSERT INTO authors (name) VALUES ('Amitav Ghosh');
INSERT INTO books (isbn, title, author_id, price, stock)
VALUES ('9780007141784', 'The Hungry Tide', LAST_INSERT_ID(), 399, 14);
SELECT b.title, a.name, a.country FROM books b JOIN authors a USING (author_id) WHERE a.name = 'Amitav Ghosh';

Result: 1 row

title	name	country
The Hungry Tide	Amitav Ghosh	India

Exercise 2: Receive 5 more copies of 'Malgudi Days' with an upsert.

solution.sql

INSERT INTO books (isbn, title, author_id, price, stock)
VALUES ('9780143039648', 'Malgudi Days', 1, 299, 5) AS n
ON DUPLICATE KEY UPDATE stock = books.stock + n.stock;
SELECT title, stock FROM books WHERE isbn = '9780143039648';

Result: 1 row

title	stock
Malgudi Days	45

12. Summary

INSERT INTO t (cols) VALUES (…), (…); adds one or many rows; always list the columns.
Omitted columns get defaults, AUTO_INCREMENT values or NULL.
LAST_INSERT_ID() returns your session's last generated ID.
INSERT … SELECT copies rows from a query.
Duplicates: INSERT IGNORE skips, ON DUPLICATE KEY UPDATE updates, REPLACE deletes and re-inserts.
From Java, use PreparedStatement batches inside a transaction.

Was this tutorial helpful?

Help Us Improve

MySQL INSERT Statement

1. Syntax

2. Inserting One Row

3. Leaving Out Columns

4. Inserting Multiple Rows

5. INSERT … SET

6. Getting the Generated ID

7. INSERT … SELECT

8. Common Insert Errors

9. Handling Duplicates

10. Inserting from Java

11. Practice Exercises

12. Summary