Insert multiple records into MySQL with a single query

Posted December 3rd, 2008 in MySql

It is possible to insert multiple records into MySQL using a comma separated list of fields. This post looks at how to do this using SQL - if you're using a nice ORM solution instead of writing SQL queries then this won't really apply.

If we have an example table that was created like this:

CREATE TABLE example (
  example_id INT NOT NULL,
  name VARCHAR( 50 ) NOT NULL,
  value VARCHAR( 50 ) NOT NULL,
  other_value VARCHAR( 50 ) NOT NULL
)

Normally we could insert a record like this:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1');

To insert more than one record at once, we can do this, with each set of field values separated by a comma:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

You could also omit the fieldnames like this:

INSERT INTO example
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

but it's best practise to include the fieldnames in case the table schema changes at a later date. If this happened then your query would break because the number and/or types of fields in your query would be different from the table structure.