How can I set the IDENTITY_INSERT option for a JDBC PreparedStatement?

Question

I need to copy data into an MSSQLServer 2005 database table which has an identity column.

I've seen how to disable the identity column by executing

SET IDENTITY_INSERT <table> ON

before the insert queries.

How can I do this when I'm using PreparedStatements to do batch inserts and I can't change the statement during the operation?

Answer 1

D'oh. Easy, figured it out I think.

Create a Statement first, execute SET IDENTITY_INSERT ON. Close statement.

Create PreparedStatement, do batch stuff, close preparedstatement.

Create a Statement, execute SET IDENTITY_INSERT OFF. Close and tidy up.

Welcome any refinements or advice on issues with this...

Answer 2

You can include SET IDENTITY_INSERT ON/OFF as part of the prepared statement. This way you only execute one command from the perspective of the client.