• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Insert multiple records into MySQL with a single query

Insert multiple records into MySQL with a single query

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.

Check Out These Related posts:

  1. CSS cursor property
  2. Get a MySQL table structure from the INFORMATION_SCHEMA
  3. Show indexes for a table with MySQL
  4. MySQL utility commands

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap