1

I am using IDENTITY columns in my SQL Server tables.

I have a unit test where I delete a data record with a certain id.

When I insert the testdata I get this exception:

Cannot insert explicit value for identity column in table 'MyTablename' when IDENTITY_INSERT is set to OFF

Then I do a quick test in my management studio:

SET IDENTITY_INSERT MyTablename ON

It says Command(s) completed successfully.

I rerun the unit test but still get the same error.

How do I go about that in general? I want that my unit testing database with all tables should allow me to insert a value manually for an identity column.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Pascal
  • 12,265
  • 25
  • 103
  • 195
  • That's weird -- I just did that today and it worked just fine. I was only using SQL Server, not also C# (I was inserting from within management studio). – thursdaysgeek Feb 22 '12 at 22:23

1 Answers1

3

SET IDENTITY_INSERT is session-specific. You can't set it to true in SSMS and then go run a unit test in some other rig. The SET command needs to be part of the unit test code.

Also, you can only set this setting to one table at a time, so it doesn't seem like your unit test will work very well unless you set each table as you need it.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • So I have to set it on Table1 to ON when I run a unit test. When I do another test on Table2 I have first to set it to OFF at TAble1 ? Then set it to ON at Table2 ? Might I assume that the "SET IDENTITY_INSERT MyTablename ON" can be put in a CommandText and ExecuteNonQuery will SET it ? – Pascal Feb 22 '12 at 22:43
  • I'm not sure if a separate call to ExecuteNonQuery will persist it beyond that call - depends on how your connection is harnessed I suspect. It is probably safer to modify the batch that actually inserts into tableX - put SET IDENTITY_INSERT ON at the beginning of the batch, and set it back off at the end (or before setting it for tableY, tableZ etc). – Aaron Bertrand Feb 22 '12 at 22:45
  • worked like a charm. Seems I have to extend my sql unit test helper :P. Thanks Bertrand :D – Pascal Feb 22 '12 at 22:51