I need to insert multiple rows into SQL Server database (100 at a time) from my Java code. How can I do this? Currently I am inserting one by one and this does not look efficient.
Add a comment
|
3 Answers
You can use PreparedStatement#addBatch() to create a batch and executeBatch() to execute it.
Connection connection = null;
PreparedStatement statement = null;
try {
connection = database.getConnection();
statement = connection.prepareStatement(SQL);
for (int i = 0; i < items.size(); i++) {
Item item = items.get(i);
statement.setString(1, item.getSomeValue());
// ...
statement.addBatch();
if ((i + 1) % 100 == 0) {
statement.executeBatch(); // Execute every 100 items.
}
}
statement.executeBatch();
} finally {
if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
}
See also:
2 Comments
Kaddy
in this approach if one record from the batch fails what will happen..? will the records after the failed record be inserted...how do i make sure that except the record that fails all other records be inserted...?
BalusC
That actually depends on the driver used. See
executeBatch() javadoc.
You can pass one very long string to SQL with multiple inserts as one statement to SQL Server. This won't work if you're doing parameterized queries, though. And concatenated SQL strings are "Generally a Bad Idea."
You might be better off looking at the BULK INSERT command. It has the problem of being rigid about column orders and such. But its WAY FAST!!
