CODEWITHSUNDEEP
mysqlsqldatabaseinsert
Genadinik
Genadinik

Reputation: 18649

How to do a batch insert in MySQL

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?

Upvotes: 214

Views: 431771

Answers (6)

Anton Magurski
Anton Magurski

Reputation: 33

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

Upvotes: 1

Mr Lou
Mr Lou

Reputation: 1817

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

Please refer to MySQL documentation on INSERT Statement

Upvotes: 11

nietaki
nietaki

Reputation: 9018

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);

Upvotes: 388

Syed Adeel Ali Rizvi
Syed Adeel Ali Rizvi

Reputation: 111

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

Upvotes: 11

Kamran
Kamran

Reputation: 853

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

Upvotes: 27

bensiu
bensiu

Reputation: 25604

mysql allows you to insert multiple rows at once INSERT manual

Upvotes: 2

Related Questions