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.





MySQL Crash Course
MySQL Crash Course
ISBN: 0672327120
EAN: 2147483647
Year: 2004
Pages: 214
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net