How to do a batch insert in MySQL

Question

I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there a better way?

Please read the documentation for a statement or function before attempting to use it. [`INSERT` supports this natively](http://dev.mysql.com/doc/refman/5.5/en/insert.html)! — Lightness Races in Orbit, Apr 03 '11 at 01:22
If you have a really large number of records and could format them as a CSV file, check out the LOAD DATA INFILE statement or mysqlimport command. — squawknull, Apr 03 '11 at 01:30
For the record, [LOAD DATA](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) is a very flexible command that does not *require* CSV input; any text format will do, and there are a number of helpful parameters for parsing and manipulating input data. This is definitely the *fastest* way to load data into a local db. It's not clear what is meant by "best" above: i.e. whether simplicity (use INSERT statements) trumps speed (use LOAD DATA). — EdwardG, Jun 01 '20 at 21:17

score 377 · Accepted Answer · edited Jun 02 '16 at 09:58

377

From the MySQL manual

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

edited Jun 02 '16 at 09:58

Jakub Matczak

15,341
5
46
64

answered Apr 03 '11 at 01:18

nietaki

8,758
2
45
56

10

Is this slower than Load Data Infile? – srchulo Nov 06 '12 at 20:59
1

what is syntax to write this insert statement in stored procedure? – Nitin Sawant Aug 22 '13 at 13:09
1

@Nitin wouldn't it be the same syntax..??? That's what I would do in SQL Server anyway. – Ads Oct 10 '13 at 00:07
26

Please note that while the question is labelled "How to do *batch* inserts" this answer is actually *bulk* insert. Bulk is usually faster, see [this question](http://stackoverflow.com/questions/11389449/performance-of-mysql-insert-statements-in-java-batch-mode-prepared-statements-v) – Mike Demenok Dec 17 '13 at 03:51
SQL Server limits number of rows per insert to 1,000. Does MySQL have a limit? – BenR Apr 04 '14 at 16:46
Do you know how many values you can insert in one go? What if I have 10,000 VALUES blocks? – Pringles Mar 26 '15 at 13:53
3

@Koffeehaus Per [this SO answer](http://stackoverflow.com/a/3536148/1554982) from @Lukman, number of values/rows that can be inserted is limited by [`max_allowed_packet`](http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet) – Sepster Sep 05 '15 at 12:43

score 27 · Answer 2 · answered May 30 '12 at 23:18

27

Most of the time, you are not working in a MySQL client and you should batch inserts together using the appropriate API.

E.g. in JDBC:

connection con.setAutoCommit(false); 
PreparedStatement prepStmt = con.prepareStatement("UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);                 
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();

prepStmt.setString(1,mgrnum2);                        
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();

int [] numUpdates=prepStmt.executeBatch();

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/tjvbtupd.htm

answered May 30 '12 at 23:18

Kamran

843
1
8
19

6

A good blog entry on batch inserts (in Java, but it's relevant to any language): http://viralpatel.net/blogs/batch-insert-in-java-jdbc/ – Kangur Oct 03 '12 at 08:21
Even though this is an example, it's probably a good idea turn auto commit on again after executing the batch. Might also be useful to briefly why it is useful as well (can improve performance for batches). Oh wow just realised how old this q/q is. – Ben Jan 05 '22 at 01:04

score 11 · Answer 3 · answered Mar 19 '13 at 05:49

Load data infile query is much better option but some servers like godaddy restrict this option on shared hosting so , only two options left then one is insert record on every iteration or batch insert , but batch insert has its limitaion of characters if your query exceeds this number of characters set in mysql then your query will crash , So I suggest insert data in chunks withs batch insert , this will minimize number of connections established with database.best of luck guys

How about `LOAD DATA LOCAL INFILE '/users/name/txt.file'` – double_j Nov 17 '14 at 18:39 — double_j, Nov 17 '14 at 18:39

score 11 · Answer 4 · edited Jul 17 '20 at 12:55

11

Insert into table(col1,col2) select col1,col2 from table_2;

Please refer to MySQL documentation on INSERT Statement

edited Jul 17 '20 at 12:55

Serg

2,346
3
29
38

answered Jan 05 '12 at 08:47

Mr Lou

1,757
19
19

how about `insert into select from ` performance? is it as fast as bulk insert? – hiway Jan 30 '18 at 02:53

score 2 · Answer 5 · answered Apr 03 '11 at 01:25

2

mysql allows you to insert multiple rows at once INSERT manual

answered Apr 03 '11 at 01:25

bensiu

24,660
56
77
117

Anton Magurski · Answer 6 · 2022-07-26T08:07:21.027

1

INSERT INTO test_1 VALUES(24, 'B', '1990-12-07'), (25, 'C', '1990-12-08');

edited Jul 26 '22 at 08:07

answered Jul 21 '22 at 22:56

Anton Magurski

33
6

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32305466) – ahuemmer Jul 25 '22 at 07:28

How to do a batch insert in MySQL

6 Answers6

Linked

Related