SQL Server Helper

Tip of the Day : How to Call NEWID Function in a User-Defined Function

Error Messages

Home > SQL Server Error Messages > Msg 544 - Cannot insert explicit value for identity column in table <Table Name> when IDENTITY_INSERT is set to OFF.

SQL Server Error Messages - Msg 544 - Cannot insert explicit value for identity column in table <Table Name> when IDENTITY_INSERT is set to OFF.

SQL Server Error Messages - Msg 544

Error Message

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

Causes:

This error occurs when you are trying to insert into a table that contains an identity column and you are assigning a value to the identity column instead of letting SQL Server assign the value. Here's an example on how it can be encountered:

CREATE TABLE [dbo].[IdentityInsert] (
    [ID]     INT NOT NULL IDENTITY(1,1),
    [Name]   VARCHAR(50))
GO

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES (1, ‘Old MacDonald’)
GO

Solution / Work Around:

There are two ways to avoid this error. First is not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record.

INSERT INTO [dbo].[IdentityInsert] ( [Name] )
VALUES (‘Old MacDonald’)
GO

The second method is with the setting of the IDENTITY_INSERT property to ON for the table.

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert] OFF

Setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table. At any given time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSErt on statement is issued for another table, SQL Server returns an error message that states Set IDENTITY_INSERT is already ON and reports the table it is set ON for. 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.