IDENTITY_INSERT的打开和关闭(how to set IDENTITY_INSERT to OFF or ON)

It appears you have a table with an IDENTITY column and you're trying to INSERT an explicit value into that column. Usually when you have an IDENTITY column you don't insert that value and SQL Server supplies the next value in sequence. You can SELECT back that value by doing a SELECT SCOPE_IDENTITY() right after the INSERT.

If you truly do want to INSERT an explicit value into an IDENTITY column do it like this.

SET IDENTITY_INSERT tableName ON

... INSERT statement here ...

SET IDENTITY_INSERT tableName OFF

After you've INSERTed an explicit value into an identity column you'll probably need to correct the see as docemented here: http://www.sqlteam.com/item.asp?ItemID=8003.

I usually only insert explicit values into an identity when I'm importing data. If you're doing during your regular processing I'd reconsider whether you really want an identity column.

Syntax is :

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } ON

转载于:https://www.cnblogs.com/nevernet/archive/2009/02/24/1396893.html