Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software to demonstrate the usage of IDENTITY_INSERT table property. ContactTypeID is defined as IDENTITY in the ContactType table. IDENTITY is the auto-numbering property.

USE AdventureWorks

GO

SELECT * from Person.ContactType

GO

-- the following will fail

INSERT Person.ContactType(ContactTypeID, [Name])

VALUES (1000, 'Email Support')

GO

/*Msg 544, Level 16, State 1, Line 3

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

*/

-- the following will succeed

SET IDENTITY_INSERT Person.ContactType ON

INSERT Person.ContactType(ContactTypeID, [Name])

VALUES (1000, 'Email Support')

-- (1 row(s) affected)

SET IDENTITY_INSERT Person.ContactType OFF

GO

SELECT * from Person.ContactType

GO

/* ContactTypeID  Name  ModifiedDate

....

1000  Email Support     2012-04-17 04:44:01.570  */

DELETE Person.ContactType WHERE ContactTypeID=1000

GO

SELECT * from Person.ContactType

GO

Related article:

IDENTITY (Property)