2. INSERT Multiple Rows

insert multiple rows into a table.

Summary: in this tutorial, you will learn how to use a single MySQL INSERTstatement to insert multiple rows into a table.

MySQL INSERT multiple rows statement

To insert multiple rows into a table, you use the following form of the INSERTstatement:

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

In this syntax:

First, specify the name of the table where you want to insert multiple rows after the INSERT INTO keywords.
Second, list the columns in the table into which you want to insert data. This column list is optional, but if provided, you should provide corresponding values for each column in the VALUES value.
Third, specify a comma-separated list of row data after the VALUESkeyword. Each item on the list represents a row. The number of values in each item must be the same as the number of columns in the column_list.

Note that to insert rows from a query into a table, you use the INSERT INTO … SELECT statement.

MySQL INSERT multiple rows limit

In theory, you can insert any number of rows using a single INSERT statement.

However, when the MySQL server receives an INSERT statement whose size is bigger than the value specified by the max_allowed_packet option, it issues a packet too large error and terminates the connection.

This statement shows the current value of the max_allowed_packet variable:

SHOW VARIABLES LIKE 'max_allowed_packet';

Output:

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

The number in the Value column is the number of bytes. Note that the value in your database server may be different.

To set a new value for the max_allowed_packet variable, you use the SET GLOBAL statement:

SET GLOBAL max_allowed_packet=size;

In this statement, the size is an integer that represents the number of the maximum allowed packet size in bytes.

Notice that the max_allowed_packet does not impact the INSERT INTO .. SELECT statement. The INSERT INTO .. SELECT statement can insert as many rows as you want.

MySQL INSERT multiple rows examples

Let’s take some examples of using the INSERT multiple rows statement.

1) Insert multiple rows into a table

First, create a new table called projects for the demonstration:

CREATE TABLE projects(
  project_id INT AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  start_date DATE,
  end_date DATE,
  PRIMARY KEY(project_id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert two rows into the projects table using the INSERT multiple rows statement:

INSERT INTO projects(name, start_date, end_date)
VALUES
  ('AI for Marketing', '2019-08-01', '2019-12-31'),
  ('ML for Sales', '2019-05-15', '2019-11-20');Code language: SQL (Structured Query Language) (sql)

MySQL issued the following message:

Query OK, 2 rows affected (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the statement has inserted two rows into the projectstable successfully.

Third, retrieve data from the projects table to verify the inserts:

SELECT * FROM projects;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+------------------+------------+------------+
| project_id | name             | start_date | end_date   |
+------------+------------------+------------+------------+
|          1 | AI for Marketing | 2019-08-01 | 2019-12-31 |
|          2 | ML for Sales     | 2019-05-15 | 2019-11-20 |
+------------+------------------+------------+------------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

2) Using the LAST_INSERT_ID() function

When you insert multiple rows and use the LAST_INSERT_ID() function to get the last inserted id of an AUTO_INCREMENT column, you will get the id of the first inserted row, not the id of the last inserted row. For example:

First, insert three rows into the projects table:

INSERT INTO projects(name, start_date, end_date)
VALUES
  ('NeuroSynthIQ', '2023-12-01', '2024-12-31'),
  ('QuantumMind Explorer', '2023-05-15', '2024-12-20'),
  ('SentientBot Assistant', '2023-05-15','2024-10-20');Code language: SQL (Structured Query Language) (sql)

Second, retrieve data from the projects table:

SELECT * FROM projects;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+-----------------------+------------+------------+
| project_id | name                  | start_date | end_date   |
+------------+-----------------------+------------+------------+
|          1 | AI for Marketing      | 2019-08-01 | 2019-12-31 |
|          2 | ML for Sales          | 2019-05-15 | 2019-11-20 |
|          3 | NeuroSynthIQ          | 2023-12-01 | 2024-12-31 |
|          4 | QuantumMind Explorer  | 2023-05-15 | 2024-12-20 |
|          5 | SentientBot Assistant | 2023-05-15 | 2024-10-20 |
+------------+-----------------------+------------+------------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

Third, get the last inserted id:

SELECT LAST_INSERT_ID();Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output shows that the LAST_INSERT_ID() returns the id of the first row in the three rows, not the id of the last row.

Summary

Use the MySQL INSERT statement to insert multiple rows into a table.