MySQL insert multiple rows

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

One INSERT can carry as many rows as you like. Separate each row’s brackets with a comma. That is less typing than 1 statement per row, and it is 1 trip to the server rather than many. These examples use the sakila_practice database from INSERT. Run the setup block there again before you start, then open the client with mysql -u root -p sakila_practice.

How a multi-row INSERT works

INSERT INTO TABLE_NAME (COLUMN_LIST) VALUES
  (FIRST_ROW_VALUES),
  (SECOND_ROW_VALUES);

Replace TABLE_NAME with the table, COLUMN_LIST with the columns you are supplying, and each of FIRST_ROW_VALUES and SECOND_ROW_VALUES with 1 value for each of those columns. The column list is written once and governs every row. Each set of brackets supplies values in that order.

Examples

1) 3 rows in 1 statement

INSERT INTO member (first_name, last_name, email, joined) VALUES
  ('Jennifer', 'Davis',  'jennifer.davis@example.com',  '2026-03-18'),
  ('Barbara',  'Jones',  'barbara.jones@example.com',   '2026-03-19'),
  ('Elizabeth','Brown',  'elizabeth.brown@example.com', '2026-03-21');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

The second line appears whenever a statement can write more than 1 row, which covers a multi-row VALUES list and any INSERT SELECT. Records counts the rows you sent, Duplicates the rows that collided with a row already stored, and Warnings anything the server had to adjust. All 3 numbers agreeing with what you sent is the sign that nothing was quietly changed.

SELECT member_id, first_name, last_name FROM member;

+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
|         1 | Mary       | Smith     |
|         2 | Patricia   | Johnson   |
|         3 | Linda      | Williams  |
|         4 | Jennifer   | Davis     |
|         5 | Barbara    | Jones     |
|         6 | Elizabeth  | Brown     |
+-----------+------------+-----------+
6 rows in set (0.00 sec)

The ids run on in the order the rows were written.

One bad row throws the whole statement away

The statement below carries 3 rows. The middle one reuses Mary Smith’s address, which the UNIQUE index on email refuses.

INSERT INTO member (first_name, last_name, email, joined) VALUES
  ('Maria',  'Miller', 'maria.miller@example.com', '2026-04-02'),
  ('Susan',  'Wilson', 'mary.smith@example.com',   '2026-04-03'),
  ('Margaret','Moore', 'margaret.moore@example.com','2026-04-04');

ERROR 1062 (23000): Duplicate entry 'mary.smith@example.com' for key 'member.uq_member_email'

The other 2 rows did not land either.

SELECT COUNT(*) AS members FROM member;

+---------+
| members |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

A single statement is all-or-nothing, so a batch of 10,000 rows with 1 bad value writes nothing at all. All-or-nothing also means a large batch needs its data checked before it is sent, because the server names 1 offending row and stops.

INSERT IGNORE turns the error into a warning

INSERT IGNORE keeps the rows it can and skips the rest.

INSERT IGNORE INTO member (first_name, last_name, email, joined) VALUES
  ('Maria',  'Miller', 'maria.miller@example.com', '2026-04-02'),
  ('Susan',  'Wilson', 'mary.smith@example.com',   '2026-04-03');
SHOW WARNINGS;

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'mary.smith@example.com' for key 'member.uq_member_email' |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

Records: 2 Duplicates: 1 reports exactly what happened: 2 rows offered, 1 refused. SHOW WARNINGS then names it, and the same ERROR 1062 text arrives as a warning rather than as a failure.

SELECT COUNT(*) AS members FROM member;

+---------+
| members |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

Use IGNORE when a duplicate genuinely means “already have it”. It is a blunt instrument otherwise, because it downgrades other errors too, including a value too long for its column and a row with no value for a required column. Run SHOW WARNINGS after every INSERT IGNORE or you will not know what it dropped. When the right answer is to update the row already stored, use upsert instead.

Summary

Separate each row’s brackets with a comma, and write the column list once.
Read Records, Duplicates and Warnings to confirm nothing was adjusted.
Expect 1 rejected row to abort the entire statement and write nothing.
Use INSERT IGNORE to keep the good rows, and read SHOW WARNINGS afterwards.
Prefer 1 multi-row statement over many single-row ones.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a multi-row INSERT works

Examples

1) 3 rows in 1 statement

One bad row throws the whole statement away

INSERT IGNORE turns the error into a warning

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a multi-row INSERT works

​Examples

​1) 3 rows in 1 statement

​One bad row throws the whole statement away

​INSERT IGNORE turns the error into a warning

​Summary

​See also

How a multi-row INSERT works

Examples

1) 3 rows in 1 statement

One bad row throws the whole statement away

INSERT IGNORE turns the error into a warning

Summary

See also