Multiple row insert in SQL Server from Java

Question

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.

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 a batch.

Check out the addBatch(), executeBatch(), etc. methods of Java's Statement

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

Answer 3

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!!