SQL Server: SET IDENTITY_INSERT {ON | OFF} Statement

I needed to add a column to one of our main table. I get the employee table as an example. I needed to add   “Unknown” Employee with a unique surrogate key. When we think surrogate key column is an identity column to that table. Following statement helped me to insert that row to employee table.

Syntax

SET IDENTITY_INSERT [database name]. [Schema name].table {ON | OFF} 

If we want to force a value of our choice needed to insert then we use SET IDENTITY_INSERT table Name ON

E.g.  Created Example table with an identity column

CREATE TABLE [dbo].[Employee](

            [Emp_ID] [int] IDENTITY(1,1) NOT NULL,

            [Emp_Code] [varchar](20) NULL,

            [Emp_FullName] [varchar](35) NULL,

            [Emp_LastName] [varchar](152) NULL

)

           

GO

Pushing data to the identity column table without the command. When you execute the query you can see the following error:

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

INSERT INTO [dbo].[Employee]

           ([Emp_ID]

           ,[Emp_Code]

           ,[Emp_FullName]

           ,[Emp_LastName]

           )

     VALUES

           (0

           ,1

           ,'UNKNOWN'

           ,'UNKNOWN')

Using SET IDENTITY_INSERT <Table Name> ON command helps to push data to the table.

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT INTO [dbo].[Employee]

           ([Emp_ID]

           ,[Emp_Code]

           ,[Emp_FullName]

           ,[Emp_LastName]

           )

     VALUES

           (0

           ,1

           ,'UNKNOWN'

           ,'UNKNOWN')

    

SET IDENTITY_INSERT [dbo].[Employee] OFF

Ref: https://msdn.microsoft.com/en-us/library/ms188059.aspx