How to do a batch insert in MySQL
Solution 1
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);
Solution 2
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();
Solution 3
Insert into table(col1,col2) select col1,col2 from table_2;
Please refer to MySQL documentation on INSERT Statement
Solution 4
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
Solution 5
mysql allows you to insert multiple rows at once INSERT manual
Related videos on Youtube
Genadinik
Thank you to everyone who has helped me. I am extremely appreciative of the amazing people on StackOverflow who have helped me :) I currently run https://www.problemio.com under which I offer mobile apps, books, coaching, and online courses, and even a B2B course licensing business: https://www.problemio.com/udemy/white-labeling-or-buying-udemy-courses.html I also run a funny t-shirt store: https://www.waveifyoulike.com
Updated on July 18, 2020Comments
-
Genadinik almost 4 years
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?
-
Lightness Races in Orbit about 13 yearsPlease read the documentation for a statement or function before attempting to use it.
INSERT
supports this natively! -
squawknull about 13 yearsIf 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.
-
EdwardG about 4 yearsFor the record, LOAD DATA 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).
-
-
Kangur over 11 yearsA good blog entry on batch inserts (in Java, but it's relevant to any language): viralpatel.net/blogs/batch-insert-in-java-jdbc
-
srchulo over 11 yearsIs this slower than Load Data Infile?
-
Nitin Sawant almost 11 yearswhat is syntax to write this insert statement in stored procedure?
-
Ads over 10 years@Nitin wouldn't it be the same syntax..??? That's what I would do in SQL Server anyway.
-
Mike Demenok over 10 yearsPlease 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
-
BenR about 10 yearsSQL Server limits number of rows per insert to 1,000. Does MySQL have a limit?
-
double_j over 9 yearsHow about
LOAD DATA LOCAL INFILE '/users/name/txt.file'
-
Koffeehaus over 9 yearsDo you know how many values you can insert in one go? What if I have 10,000 VALUES blocks?
-
Sepster almost 9 years@Koffeehaus Per this SO answer from @Lukman, number of values/rows that can be inserted is limited by
max_allowed_packet
-
hiway over 6 yearshow about
insert into select from
performance? is it as fast as bulk insert? -
Ben over 2 yearsEven 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.