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

User · Accepted Answer

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

User · Answer

mysql allows you to insert multiple rows at once INSERT manual

User · Answer

Insert into table col1 col2  select col1 col2 from table 2   Please refer to MySQL documentation on INSERT Statement

User · Answer

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

User · Answer

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

[mysql] How to do a batch insert in MySQL

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to insert