How to insert multiple rows with single MySQL query?

You can insert multiple rows with the help of values() separated by comma(,). The syntax is as follows −

insert into yourTableName values(value1,value2,...N),(value1,value2,...N),(value1,value2,...N),(value1,value2,...N),(value1,value2,...N),(value1,value2,...N)................N;

To insert multiple rows, let us create a table. The following is the query to create a table −

mysql> create table MultipleRowsInsert
   −> (
   −> UserId int,
   −> UserName varchar(200)
   −> );
Query OK, 0 rows affected (1.21 sec)

Here is the query to insert multiple rows in the table −

mysql> insert into MultipleRowsInsert values(100,'Bob'),(101,'Smith'),(102,'Carol'),(104,'David'),(105,'Sam');
Query OK, 5 rows affected (0.33 sec)
Records: 5 Duplicates: 0 Warnings: 0

Display all the records using the SELECT −

mysql> select *from MultipleRowsInsert;

The following is the output −

+--------+----------+
| UserId | UserName |
+--------+----------+
|    100 | Bob      |
|    101 | Smith    |
|    102 | Carol    |
|    104 | David    |
|    105 | Sam      |
+--------+----------+
5 rows in set (0.00 sec)

Jennifer Nicholas

Published on 11-Jan-2019 10:16:02

Previous Page Print Page