I have a few large CSV files to upload to an instance of SQL Server.(Nope, can't use SSMS) I'm using JDBC to read the CSVs line by line and insert the lines in a table in batches. Supposing my code is as such:

val connection = DriverManager.getConnection(<urls and credentials>)
val stmt = connection.prepareStatement("insert into blah_table values(?,?,?,?,?,?,?)")
while(<csvReader has next>)
    //populate the stmt with values
    // add a batch

Say the CSV file had some 20000 lines with some 50-60 or more columns. I encountered an error by chance where the connection to SQL Server was lost while connection.commit() was being executed. This resulted in ~11000/20000 lines being inserted. I went through my application several times hoping to find some error I might have made. Everything appears to be fine. My question is, is it possible that if the connection is lost in between committing, partial commits are left inserted? If so, what could be a way out to save from partial commits?

  • 1
    Maybe try to open an explicit transaction before you start inserting? Apr 23, 2018 at 10:45
  • 1
    Setting auto-commit to false does that already, though. It's the same as BEGIN TRANSACTION. Let me try that anyway, though. I hope I can reproduce the error again. Apr 23, 2018 at 10:47

1 Answer 1


Short Answer

Add exception handling to ensure a rollback happens on error.

try {
    <code to execute>
} catch (SQLException ex) {
} finally {


if autocommit is set to false, and no connection.rollback() was issued before the program exits, the drivers call an automatic connection.commit() before it closes the connection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.