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)
Published on 11-Jan-2019 14:46:02
