SQL

Batch insert rows into MySQL table


Problem

If you worked with SQL before you've probably been using INSERT to insert rows into a table. A less known feature is the ability to insert multiple rows with one query.



Solution

If you want to insert two persons into a table, you can simply call INSERT INTO twice:

INSERT INTO persons (name, age) VALUES ('Phil', 36);
INSERT INTO persons (name, age) VALUES ('Mary', 50);

That works just fine, but there's also a way to do the same with one query:

INSERT INTO persons (name, age) VALUES ('Phil', 36), ('Mary', 50)

The difference is small, but the second query only sends a single command to the server. You will have a hard time to see an impact on performance if you just add two rows, but if you batch process a lot of data you gain a measurable speed difference if you insert multiple rows with one command.

Comments




Please sign-in to post a comment