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 (http://blog.SQLAuthority.com) , BOL

About these ads

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

  1. 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”?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s