SQL SERVER - Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table

Server: Msg 544, Level 16, State 1, Line 1Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

This error message appears when you try to insert a value into a column for which the IDENTITY property was declared, but without having set the IDENTITY_INSERT setting for the table to ON.

Fix/WorkAround/Solution:
--Turn Identity Insert ON so records can be inserted in the Identity Column
SET IDENTITY_INSERT [dbo].[TableName] ON
GO
INSERT INTO [dbo].[TableName] ( [ID], [Name] )VALUES ( 2, 'InsertName')
GO
--Turn Identity Insert OFF
SET IDENTITY_INSERT [dbo].[TableName] OFF
GO


Setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table. Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.