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.
Dustin Laine
2010-10-01 17:35:17
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ć
2010-10-01 17:40:23
Thanks, a little Friday morning aixelsyd. :)
Dustin Laine
2010-10-01 17:44:32
You still have it the other way around.
Ivan Ferić
2010-10-01 17:56:35
+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ć
2010-10-01 17:37:03