How Can we Insert multiple records into MySQL with a single query?

Posted on

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');
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s