2

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?

brabster
  • 42,504
  • 27
  • 146
  • 186

3 Answers3

4

@Brabster A few years late, but it may be useful to anyone who needs to do something similar. You can set identity_insert on and off with concatenated statements within the string you pass to prepareStatement():

myStatement.prepareStatement("SET IDENTITY_INSERT MY_TABLE ON;INSERT INTO MY_TABLE ...;SET IDENTITY_INSERT MY_TABLE OFF");
Code Different
  • 90,614
  • 16
  • 144
  • 163
onoma
  • 93
  • 2
  • 11
2

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

Yishai
  • 90,445
  • 31
  • 189
  • 263
brabster
  • 42,504
  • 27
  • 146
  • 186
  • You should be able to do this as part of one prepared statement, you just have to separate the lines. – Yishai Jun 15 '09 at 12:48
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.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thanks, but how? Say I have: INSERT INTO NAMES_TABLE (id,name) VALUES (?,?) Which I want to use addBatch to do lots of inserts quickly. How would I modify this to do the SET... part as well? – brabster Jun 15 '09 at 12:55