How to do a bulk insert in MySQL?

Last updated on September 11th, 2020 at 11:31 am

A bulk insert in MySQL is when you need to insert a lot of records in a table at one time. It can be used to quickly create data for testing. It is tiresome to rewrite the insert statement for every row. Here’s how you can bulk insert in MySQL.

Bulk Insert in MySQL

Syntax

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( a1, a2,...aN ),( b1, b2,...bN ),..;

a1, a2, a3,.. – values for field1
b1, b2, b3,.. – values for field2
c1, c2, c3,.. – values for field3
…

Example
For a table table_name with columns a, b, c

mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(3,3,3);
+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   3  |
+------+------+------+

As you can see, bulk insert in MySQL allows duplicate rows/columns. It may cause duplicate primary keys. Here’s how you can avoid it.

Bulk insert in MySQL without duplicates

We use ON DUPLICATE KEY clause. It detects duplicate values of primary key. We also add an UPDATE clause to update the duplicate value.

Syntax

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( a1, a2,...aN ),( b1, b2,...bN ),..
                       ON DUPLICATE KEY
                       UPDATE primary_key_field=<expression> ;

a1, a2, a3,.. – values for field1
b1, b2, b3,.. – values for field2
c1, c2, c3,.. – values for field3
…

Example
For a table table_name with columns a, b, c and c is the primary key

mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(3,3,3)
ON DUPLICATE KEY UPDATE c=c+1;
+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   4  |
+------+------+------+

You can also update value based on other columns

mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(1,2,3)
ON DUPLICATE KEY UPDATE c=value(a)+value(b);
+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   6  |
+------+------+------+