Sql Server: Insert Explicit Value for Identity Column in Table

Explicit value can be inserted for indenity column in a table. It is possible by SET IDENTITY_INSERT tablename ON. In following example “HumanResources.Department” is used. In this table DepartmentID is an identity column.

USE AdventureWorks

GO

-- check already inserted records in table

SELECT  *

FROM    HumanResources.Department

GO

-- for explicit insert use IDENTITY_INSERT -- ON

SET IDENTITY_INSERT HumanResources.Department ON

INSERT  INTO HumanResources.Department

        (DepartmentID,Name,GroupName)

        SELECT  55,'TEST','TEST'

GO

-- to resume implicit insert in identity column use IDENTITY_INSERT -- OFF

SET IDENTITY_INSERT HumanResources.Department OFF

INSERT  INTO HumanResources.Department ( Name, GroupName )

        SELECT  'TEST2','TEST2'

-- SELECT to check newly inserted values       

SELECT  *

FROM    HumanResources.Department

When you resume implicit insertion in identity column, next value for identity column will be the value next to maximum existing value. In our case it will be 56 as we implicitly inserted 55.