INSERT - Multiple-row insertions

This format of the MySQL statement, INSERT allows one SQL statement to insert multiple rows into a MySQL table.


Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

  [INTO] table [(column,...)]
  VALUES ({expression|DEFAULT},...),(...)
  [ON DUPLICATE KEY UPDATE column=expression,...]


This format of the MySQL statement, INSERT allows one SQL statement to insert multiple rows into a table. The columns in which data is to be inserted may be given in parentheses in a comma-separated list. If no columns are specified, the statement must include a value for each column in each row, in the order that they appear in the table. In the place reserved for an AUTO_INCREMENT column, specify NULL and the server will insert the correct next value in the column. To specify default values for other columns, use the DEFAULT keyword. NULL may also be given for any other column that permits NULL and that you wish to leave NULL. The VALUES clause lists the values of each row to be inserted into a MySQL table. The values for each row are enclosed in parentheses; each row is separated by a comma.


INSERT INTO clients (client_name, telephone)
VALUES('Marie & Associates', '504-486-1234'),
('Geoffrey & Company', '617-522-1234'),
('Kenneth & Partners', '617-523-1234');

In this example, three rows are inserted into the clients table with one MySQL statement. Although the table has several columns, only two columns are inserted for each row here. The other columns are set to their default or to NULL. The order of the values for each row corresponds to the order that the columns are listed.

Normally, with MySQL if a multiple INSERT statement is entered and one of the rows to be inserted is a duplicate, an error is triggered and an error message is displayed. The statement is terminated and no rows are inserted. The IGNORE keyword, however, instructs the server to ignore any errors encountered, suppress the error messages, and insert only the non-duplicate rows. The results of this MySQL statement display like so:

Query OK, 120 rows affected (4.20 sec)
Records: 125  Duplicates: 5  Warnings: 0

These results indicate that 125 records were to be inserted, but only 120 rows were affected or successfully inserted. There were five duplicates in the MySQL statement, but there were no warnings because of the IGNORE keyword. Entering the SHOW WARNINGS statement will display the suppressed warning messages.