Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
2K+ Views
Advertisements