|
|
Error Message
Server: Msg 8106, Level 16, State 1, Line 1
Table '<Table Name>' does not have the identity
property. Cannot perform SET operation.
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 is the name of the table with an identity column.
Here’s an example on how the SET IDENTITY_INSERT statement is used:
CREATE TABLE [dbo].[User] (
[UserID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[UserName] VARCHAR(20),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
SET IDENTITY_INSERT [dbo].[User] ON
INSERT INTO [dbo].[User] ( [UserID], [UserName], [FirstName], [LastName] )
VALUES ( 1, 'mickeymouse', 'Mickey', 'Mouse' )
SET IDENTITY_INSERT [dbo].[User] OFF
The SET IDENTITY_INSERT statement can only be issued to tables with an identity column. If the table name passed to this statement does not have an identity column, this error message (Msg 8106) will be generated, as can be seen from the following script:
CREATE TABLE [dbo].[Currency] (
[CurrencyCode] CHAR(3) NOT NULL PRIMARY KEY,
[CurrencyName] VARCHAR(50)
)
SET IDENTITY_INSERT [dbo].[Currency] ON
Msg 8106, Level 16, State 1, Line 1
Table 'dbo.Currency' does not have the identity property. Cannot perform SET operation.
Solution / Work Around:
This error can easily be avoided by making sure that the table being passed in the parameter of the SET IDENTITY_INSERT statement has an identity column. If the SET IDENTITY_INSERT statement is executed from a script that loops through a list of tables wherein some tables have an identity column while others don’t have an identity column, the OBJECTPROPERTY function can be used to determine if a given table has an identity column or not and based on the output of the function, the SET IDENTITY_INSERT statement can be executed.
The OBJECTPROPERTY function returns information about schema-scoped objects in the current database and its syntax is as follows:
OBJECTPROPERTY ( <id>, <property> )
The <id> parameter is an expression that represents the ID of the object in the current database. The <id> parameter is an int data type and is assumed to be a schema-scoped object in the current database context. The <property> parameter is an expression that represents the information to be returned for the object specified by <id>. In the case of determining if a table contains an identity column, the value of 'TableHasIdentity' will be passed to this parameter.
To get the ID of an object such as a user-table, the OBJECT_ID function can be used. The OBJECT_ID function returns the database object identification number of a schema-scoped object and has the following syntax:
OBJECT_ID ( '[ <database_name> . [ <schema_name> ] . | <schema_name>. ] <object_name>'
[ , '<object_type>' ] )
The <object_name> parameter is the object to be used whose identification number is being determined. The <object_type> parameter is the schema-scoped object type.
Given both the OBJECTPROPERTY function as well as the OBJECT_ID function, the following script can be used to determine if a table has an identity column before the SET IDENTITY_INSERT statement is executed and therefore avoiding this error message (Msg8106):
IF OBJECTPROPERTY(OBJECT_ID('dbo.Currency'), 'TableHasIdentity') = 1
SET IDENTITY_INSERT [dbo].[Currency] ON
|