Insert Multiple Rows in MySQL

Adding many rows

You often need to add many rows at once — a list of students, products or records. Running one INSERT per row works but is slow. MySQL lets you insert many rows in a single INSERT, which is faster and cleaner.

One row at a time

INSERT INTO students (roll, name) VALUES (1, 'Aman');
INSERT INTO students (roll, name) VALUES (2, 'Diya');
INSERT INTO students (roll, name) VALUES (3, 'Kabir');

This works, but each statement is processed separately. For a few rows it is fine; for many, it is slow and repetitive.

Many rows in one INSERT

INSERT INTO students (roll, name) VALUES
    (1, 'Aman'),
    (2, 'Diya'),
    (3, 'Kabir');

List each row's values in parentheses, separated by commas. All three rows are added in one statement — shorter to write and much faster to run.

Why it is faster

A single multi-row INSERT is processed as one operation, with far less overhead than sending and running many separate statements. For loading hundreds or thousands of rows, this makes a huge speed difference — one of the easiest performance wins in SQL.

Insert from a SELECT

INSERT INTO archive (id, name)
SELECT id, name FROM users WHERE active = 0;

INSERT INTO ... SELECT copies rows from one table into another. Here inactive users are copied into an archive table in a single statement — no manual typing of values.

Handling duplicates

-- skip rows that clash with a unique key
INSERT IGNORE INTO students (roll, name) VALUES (1, 'Aman');

-- or update the existing row instead
INSERT INTO students (roll, name) VALUES (1, 'Aman Kumar')
    ON DUPLICATE KEY UPDATE name = 'Aman Kumar';

INSERT IGNORE skips rows that would cause a duplicate-key error, and ON DUPLICATE KEY UPDATE updates the existing row. Both let a bulk insert continue smoothly past duplicates.

Common mistakes

• Mismatched value counts. Each row's values must match the listed columns.
• Forgetting commas between rows. Separate each parenthesised set with a comma.
• Ignoring duplicate-key errors. Use INSERT IGNORE or ON DUPLICATE KEY UPDATE.
• One giant statement for millions of rows. Insert in batches to respect packet size.

Summary

• Insert many rows in one INSERT by listing value sets with commas.
• A single multi-row INSERT is much faster than many separate ones.
• INSERT INTO ... SELECT copies rows from another table.
• INSERT IGNORE and ON DUPLICATE KEY UPDATE handle duplicates.
• For huge loads, insert in batches.

Next, update many rows in updating multiple rows, or auto-number them with AUTO_INCREMENT.