MySQL INSERT Multiple Rows: Syntax and Examples

Insert multiple rows with one MySQL INSERT statement. Learn VALUES syntax, column matching, and packet-size limits for larger batches.

Use a multi-row INSERT statement to add several rows with one statement. In MySQL, put one parenthesized value list per row after VALUES, and separate the lists with commas. For the general INSERT syntax, see MySQL INSERT.

MySQL INSERT multiple rows syntax

INSERT INTO table_name (column_1, column_2)
VALUES
    (value_1a, value_2a),
    (value_1b, value_2b),
    (value_1c, value_2c);

The column list is optional, but specifying it makes the statement easier to read and less sensitive to changes in table column order. Every value list must provide one value for each named column, in the same order. If you omit the column list, provide values for all table columns in table order.

Insert several rows in one statement

The following example creates a small table and inserts three products:

CREATE TABLE insert_multi_demo (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO insert_multi_demo (product_name, price)
VALUES
    ('Notebook', 4.50),
    ('Pen', 1.25),
    ('Backpack', 32.00);

The product_id column is omitted from the insert list, so MySQL generates its AUTO_INCREMENT values. On a newly created table, you can check the rows with:

SELECT product_id, product_name, price
FROM insert_multi_demo
ORDER BY product_id;

The query returns:

+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
|          1 | Notebook     |  4.50 |
|          2 | Pen          |  1.25 |
|          3 | Backpack     | 32.00 |
+------------+--------------+-------+

Use a separate INSERT INTO ... SELECT statement when the rows come from a query or another table, rather than from literal values. For a CSV file, see Import CSV into MySQL and MySQL’s LOAD DATA statement.

Common mistakes

  • Different number of values: If the insert list names two columns, every row must contain two values.
  • Wrong value order: Values correspond to the column list from left to right. Keep each row’s values in that order.
  • Omitting a required column: A column you omit needs a usable default, allows NULL, or is generated automatically, such as an AUTO_INCREMENT column. Otherwise MySQL can reject the statement, especially in strict SQL mode.
  • Using one very large statement: A multi-row insert is still one SQL statement. Its size is subject to the max_allowed_packet limit on both the client and server. If a packet exceeds a limit, MySQL can return ER_NET_PACKET_TOO_LARGE and close the connection. Split large data sets into batches sized for your row data and client/server settings; there is no single safe row count for every workload.

For duplicate keys, INSERT IGNORE skips conflicting rows, while INSERT ... ON DUPLICATE KEY UPDATE updates them. Both differ from a plain INSERT, which reports a duplicate-key error.

Summary

  • Use one INSERT ... VALUES statement with comma-separated value lists to insert multiple rows.
  • Specify a column list and keep the value count and order consistent in every row.
  • Split very large inserts into batches that fit the client and server packet limits.

See the MySQL 8.4 Reference Manual: INSERT Statement and Packet Too Large for the complete syntax and packet-size details.