How to do a batch insert in MySQL?

To do a batch insert, we need to use all column names with parenthesis, separated by ?,'.

Let us see an example. First, we will create a table. The following is the CREATE command to create a table.

 mysql> CREATE table MultipleRecordWithValues - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.88 sec) 

The following is the syntax of batch insert.

 INSERT into yourTableName values(column1,column2,....N),(column1,column2,....N),(column1,column2,....N),...........N; 

Applying the above syntax to insert batch records.

 mysql> insert into MultipleRecordWithValues values(1,'John'),(2,'Carol'),(3,'Bob'),(4,'Smith'); Query OK, 4 rows affected (0.16 sec) Records: 4 Duplicates: 0 Warnings: 0 

Since 4 rows are affected, it implies that we have inserted records successfully. To check if all records are present in the table, use the SELECT command.

 mysql> select *from MultipleRecordWithValues; 

The following is the output.

 +------+-------+ | id | name | +------+-------+ | 1 | John | | 2 | Carol | | 3 | Bob | | 4 | Smith | +------+-------+ 4 rows in set (0.00 sec)