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

Error Message:

Server: Msg 544, Level 16, State 1, Line 1 Cannot 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.

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL

, ,
Previous Post
SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility
Next Post
SQL SERVER – Trace Flags – DBCC TRACEON

Related Posts

2 Comments. Leave new

  • Gourav Chavan
    March 22, 2012 2:05 pm

    can we use above statements inside a stored procedure ?
    my stored procedure will also use begin transaction and commit

    Reply
  • I did this and I’ve done this a million times in the past. At new job I am trying and keep coming up with the error. Is there something that could be turning it off right after it is set to “on”?

    Reply

Leave a Reply

Menu