Insert multiple rows in SQL Server with Java

Question

I need to insert multiple rows into a SQL Server database (100 at a time) from my Java code. How can i do this? I am currently inserting one by one and it does not look effective.

Answer 1

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 :

• JDBC Tutorial - Using PreparedStatement
• JDBC Tutorial - Batch Updates

Answer 2

Use the package.

Check out the addBatch (), executeBatch (), etc. methods. Java statement

For a simple example, check here (but I would suggest using PreparedStatement)

Answer 3

You can pass one very long row in SQL with multiple inserts as one of the SQL Server statements. However, this will not work if you execute parameterized queries. And SQL concatenated strings are "Generally a bad idea."

You might be better off looking at the BULK INSERT command . He has a problem being rigid in column order and the like. But its WAY IS FAST !!

Answer 4

found an example of batch processing using jdbc. check this out: http://www.exampledepot.com/egs/java.sql/BatchUpdate.html