How can I efficiently perform a batch insert to a Sql Server table using jdbc only for new rows (ID column value not present in the target table)?
Actually I am using the PreparedStatement object to create the batches, but without the "insert only if new row" logic:
String sqlInsertStub = "INSERT INTO mytable (id, col2, col3) values (?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(sqlInsertStub)) {
connection.setAutoCommit(false);
int processed = 0;
while (resultSet.next()) {
// [...]
preparedStatement.setInt(1, id);
preparedStatement.setString(2, col2);
preparedStatement.setString(3, col3);
preparedStatement.addBatch();
if (++processed % BATCH_SIZE == 0) {
preparedStatement.executeBatch();
connection.commit();
}
}
preparedStatement.executeBatch();
connection.commit();
catch (Exception e) {
connection.rollBack();
e.printStackTrace();
System.exit(1);
}