[Previous] [TOC] [Next]

Inserting Multiple Rows

INSERT inserts a single row into a table. But what if you needed to insert multiple rows? You could simply use multiple INSERT statements, and could even submit them all at once, each terminated by a semicolon, like this:

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES('Pep E. LaPew',
   '100 Main Street',
   'Los Angeles',
   'CA',
   '90046',
   'USA');
INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES('M. Martian',
   '42 Galaxy Way',
   'New York',
   'NY',
   '11213',
   'USA');

Or, as long as the column names (and order) are identical in each INSERT, you could combine the statements as follows:

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );

Here a single INSERT statement has multiple sets of values, each enclosed within parentheses, and separated by commas.

Improving INSERT Performance This technique can improve the performance of your database possessing, as MySQL will process multiple insertions in a single INSERT faster than it will multiple INSERT statements.

[Previous] [TOC] [Next]