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.

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:

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:

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