SQL Server Error Messages - Msg 545¶

Error Message¶

Server: Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table '<Table Name>' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Causes¶

The SET IDENTITY_INSERT statement allows the insertion of explicit values into the identity column of a table. The syntax of the SET IDENTITY_INSERT statement is as follows:

SET IDENTITY_INSERT [ <database_name> . [ <schema_name> ] . ] <table_name> { ON | OFF }

The <database_name> is the name of the database in which the specified table resides. The <schema_name> is the name of the schema to which the table belongs. The <table_name> is the name of the table with an IDENTITY column.

Once the IDENTITY_INSERT option is set to ON on a particular table, any INSERT operation performed on the table has to specify a value to the IDENTITY column of the table, otherwise this error will be raised.

Here’s a simple script that illustrates this scenario:

CREATE TABLE [dbo].[Company] (
    [CompanyID]       INT NOT NULL IDENTITY(1, 1),
    [CompanyName]     NVARCHAR(100) NOT NULL,
    [Address]         VARCHAR(100) NOT NULL,
    [City]            VARCHAR(50) NOT NULL,
    [State]           CHAR(2) NOT NULL,
    [ZIPCode]         VARCHAR(10) NOT NULL
)
GO

SET IDENTITY_INSERT [dbo].[Company] ON

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )

Msg 545, Level 16, State 1, Line 5
Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Msg 545, Level 16, State 1, Line 8
Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Msg 545, Level 16, State 1, Line 11
Explicit value must be specified for identity column in table 'Company' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

As can be seen from the script, the IDENTITY_INSERT option for the [dbo].[Company] table has been set to ON before the INSERT statements. Since the INSERT statements do not include an explicit value for the IDENTITY column, in this case the [CompanyID], then this error message is generated.

Solution / Work Around¶

There are a couple of ways of avoiding this error message. The first option is to set the IDENTITY_INSERT option to OFF before performing the INSERT statement. With the IDENTITY_INSERT option set to OFF, the value for the identity column will be automatically generated by SQL Server.

Here’s how the script will look like with the sample data inserted to the [dbo].[Company] table:

-- Solution #1 - SET IDENTITY_INSERT OFF
SET IDENTITY_INSERT [dbo].[Company] OFF

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO [dbo].[Company] ( [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )
SELECT * FROM [dbo].[Company]

CompanyID  CompanyName  Address                 City           State ZIPCode
---------- ------------ ----------------------- -------------- ----- --------
1          Facebook     1 Hacker Way            Menlo Park     CA    94025
2          Google       1600 Amphitheatre Pkwy  Mountain View  CA    94043
3          Microsoft    One Microsoft Way       Redmond        WA    98052

The second alternative in avoiding this error is to have the IDENTITY_INSERT option set to ON and manually assign the value to the identity column for each row of data inserted. Here’s how the script will look like:

-- Solution #2 : Include Identity Column
SET IDENTITY_INSERT [dbo].[Company] ON

INSERT INTO [dbo].[Company] ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 101, 'Facebook', '1 Hacker Way', 'Menlo Park', 'CA', '94025' )

INSERT INTO [dbo].[Company] ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 102, 'Google', '1600 Amphitheatre Pkwy', 'Mountain View', 'CA', '94043' )

INSERT INTO [dbo].[Company] ( [CompanyID], [CompanyName], [Address], [City], [State], [ZIPCode] )
VALUES ( 103, 'Microsoft', 'One Microsoft Way', 'Redmond', 'WA', '98052' )

CompanyID  CompanyName  Address                 City           State ZIPCode
---------- ------------ ----------------------- -------------- ----- --------
101        Facebook     1 Hacker Way            Menlo Park     CA    94025
102        Google       1600 Amphitheatre Pkwy  Mountain View  CA    94043
103        Microsoft    One Microsoft Way       Redmond        WA    98052