How to Insert Multiple Rows in MySQL

In today’s post, we’ll learn how to insert multiple rows in MySQL.

Insert Multiple Rows in MySQL

INSERT adds new rows to a table that already exists. Rows are added using the INSERT... VALUES command based on explicitly supplied values.

The INSERT privilege for the table is necessary to insert data into it.

Syntax:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

In the above syntax, table_name indicates the name of the table in which you wish to insert the data. Second, following the table name, give a list of columns with commas between them.

Third, include a list of row data in the VALUES clause that is comma-separated. The list’s items are each row in the data.

Each element must have the same number of values as the columns in the column list.

Theoretically, a single INSERT command may insert any number of rows. However, the MySQL server will give a packet too large error and cut off the connection if it receives an INSERT statement whose size exceeds the max allowed packet.

You may use the following line to change the max_allowed_packet variable’s value:

SET GLOBAL max_allowed_packet=size;

To further understand the previous concept, consider the following example:

INSERT INTO Employees (first_name, last_name)
VALUES
    ('John', 'Doe'),
    ('Will', 'Smith');

In the above code, we are inserting two Employees records like first_name and last_name together in the Employees table separated by ,.

Run the above code line in any browser compatible with MySQL. It will display the following outcome.

Output:

2 row(s) affected