Summary: in this tutorial, you will learn how to use a single MySQL INSERT
statement 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 INSERT
statement:
INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);
Code language: SQL (Structured Query Language) (sql)
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
VALUES
keyword. 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 thecolumn_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';
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
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 projects
table 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.