INSERT Multiple Rows
In this page:
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
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
-- 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
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
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
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: