I'm investigating the SQLServerBulkCopy class to do a bulk insert into a database. Currently, I'm using JDBC and when I attempt to perform a bad insert, I can catch a BatchUpdateException to find out what row caused the problem.
How does SQLServerBulkCopy handle error rows, will I be able to do something similar?
"How does SQLServerBulkCopy handle error rows"
If .writeToServer encounters an error it throws the exception that is returned from the server, e.g.,
com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__#tmp______3213E83F719D9EEE'. Cannot insert duplicate key in object 'dbo.#tmp'. The duplicate key value is (1).
"if I have 10k rows is it doing 10k insert statements and committing after each one?"
No, it is sending an INSERT BULK statement and then streaming batches of rows to the server according to SQLServerBulkCopyOptions#setBatchSize. The default batch size is zero (0), meaning that it would effectively stream all 10K rows in one shot. For a batch size >0 it would stream that many rows and then repeat the INSERT BULK process until all rows have been copied.
If an error is encountered, the documentation says:
In this first example, the bulk copy operation is non-transacted. All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches.
"is [the number of rows actually copied] not available in java?"
It appears not. .writeToServer returns void, and there does not appear to be a property of the SQLServerBulkCopy object to supply that information.