views:

50

answers:

3

For a sql script I'm working on, I need to programmatically remove the identity, identity seed, and identity increment for a column in an existing table, then add them back to the table at the end of the script. Does anyone have a reference or an example on how to do this?

+1  A: 

Yes, you just do this:

SET IDENTITY_INSERT [TABLE] ON

And then back on:

SET IDENTITY_INSERT [TABLE] OFF

This will allow you to enter manual data in the identity column.

http://msdn.microsoft.com/en-us/library/ms188059.aspx

Dustin Laine
You should first set it to ON, and in the end to OFF. With ON it signalizes that you are about to do manual inserts on identity column.
Ivan Ferić
Thanks, a little Friday morning aixelsyd. :)
Dustin Laine
You still have it the other way around.
Ivan Ferić
+3  A: 

You should do this:

SET IDENTITY_INSERT <TableName> ON
-- Do the inserting in the table with name <TableName>
SET IDENTITY_INSERT <TableName> OFF

For more details look in the MSDN.

Ivan Ferić