INSERT Multiple Rows

Inserting multiple rows is like handing the teacher a whole stack of new name cards at once, instead of giving them one at a time.

In this page:

Multiple Rows in One Query
Efficiency Benefits
Mixing Columns and Multiple Rows
Inserting Nulls in Multiple Rows
Handling Auto-Increment

Syntax

sql

INSERT INTO table_name (column1, column2)
VALUES (value1, value2),
       (value3, value4),
       (value5, value6);

Multiple Rows in One Query

A single INSERT INTO statement can add many rows at once by listing several comma-separated value sets in parentheses, which is both more concise and considerably faster than one INSERT per row.

Example: Multiple Rows in One Query

sql

INSERT INTO users (name) VALUES ('Amit'), ('Priya'), ('Rahul');

Efficiency Benefits

Batching inserts together significantly reduces the number of round trips between your application and the database server, cutting network overhead and letting MySQL optimize the whole batch as one operation.

Example: Efficiency Benefits

sql

-- One round trip inserts all three rows instead of three separate statements
INSERT INTO users (name) VALUES ('Amit'), ('Priya'), ('Rahul');

Mixing Columns and Multiple Rows

Every value set in a multi-row insert must supply values in the same order and for the same columns as the others — mismatched column counts across rows will cause the entire statement to fail.

Example: Mixing Columns and Multiple Rows

sql

INSERT INTO users (id, name) VALUES (1, 'Amit'), (2, 'Priya');

Inserting Nulls in Multiple Rows

You can still use the NULL keyword inside any individual row of a multi-row insert, letting some rows have missing data for a column while others in the same batch don't.

Example: Inserting Nulls in Multiple Rows

sql

INSERT INTO users (id, name) VALUES (1, 'Amit'), (2, NULL);

Handling Auto-Increment

When the primary key is AUTO_INCREMENT, you simply omit it from every row in a multi-row insert, and MySQL assigns the next sequential ID to each one automatically as it processes the batch.

Example: Handling Auto-Increment

sql

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Amit'), ('Priya');

Chapter Quiz — Complete all 6 topics to unlock

0/6 topics done

Complete these topics first:

← Previous Lesson INSERT INTO Next Lesson → SELECT Statement