How to Insert Multiple Rows in MySQL

The user can add several rows simultaneously in a table. The user can use the INSERT statement to add multiple values in the columns.

Syntax of the INSERT statement

The syntax to add multiple rows of data in MySQL is as follows:

In the above statement:

The user first must specify the table name in which they want to enter the data after beginning with the INSERT INTO keywords. Then, the user must mention the names of all the columns inside brackets after stating the table name. The order of the columns should be the same as the value to be entered in the column.

Then, use the VALUES keyword to add values in the respective columns. It can be performed by adding the values and separating them by commas. Every element mentioned in the list of values represents a different row in MySQL. The user needs to ensure that the values in each element should be the same as the number of columns specified in the list of columns.

MySQL INSERT Multiple Rows Limit

In theory, there is no upper limit to the values that can be inserted by implementing a single INSERT command. However, a parameter called max_allowed_packet limits the size of the data that can be inserted using a single INSERT statement. If the user tries to add more extensive data, MySQL will issue an error stating the packet is too large and immediately end the connection.

The user can implement the SQL statement below to check the value of the max_allowed_packet parameter:

By default, the value for the parameter is set at 1 GB. The value may differ in different servers. The user can implement a query to change the max_allowed_packet.

Here, desired_size is an integer value that refers to the maximum allowed packet size in bytes. Note that this parameter does not directly impact the INSERT INTO … SELECT statement. The user can add as many rows using the INSERT INTO … SELECT statement.

Implementation of MySQL INSERT Statement To Add Multiple Rows

Let us consider an example where the user will use the INSERT query to add multiple rows in the table simultaneously.

The first step is to create a new table in which the rows of information will be added. We have defined a demotable to implement the INSERT query in this case.

Once the user has defined the table to implement the query, the user can move to the next step to implement the INSERT query to add multiple rows of data in demotable.

Finally, you can verify the data is successfully inserted in the table by implementing the SELECT statement below:

In this tutorial, you have learned to use the INSERT statement to add multiple rows to the table.