Previous Page
Next Page

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:

Input

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:

Input

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

Analysis

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

Tip

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 Page
Next Page