script to add and remove auto-increment property from a column

sql-server identity-insert
quillbreaker picture quillbreaker · Oct 1, 2010 · Viewed 44.7k times · Source

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?

Answer

Ivan Ferić picture Ivan Ferić · Oct 1, 2010

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.

Related questions

How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?

I've searched for this, but threads in which it appeared tended to have answers from people who didn't understand the question. Take the following syntax: SET IDENTITY_INSERT Table1 ON How do you do something more like this: GET IDENTITY_…

Read More
SqlBulkCopy Insert with Identity Column

I am using the SqlBulkCopy object to insert a couple million generated rows into a database. The only problem is that the table I am inserting to has an identity column. I have tried setting the SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity …

Read More
How to automatically reseed after using identity_insert?

I recently migrated from a PostgreSQL database to a SQL Server database. To switch the data over I had to enable IDENTITY_INSERT. Well come to find out that I get all sorts of strange errors due to duplicate identity …

Read More