|
|
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
|