Error 8101

Severity Level 16

Message Text

An explicit value for the identity column in table '%.*s' can only be specified when a column list is used and IDENTITY_INSERT is ON

Explanation

You have attempted to insert a row, containing a specific identity value, into a table which contains an identity column. However, you did not provide a column list or have the IDENTITY_INSERT property enabled for the specified table.

Action

To successfully insert a specific identity row in a table containing an identity column you must provide a column list and enable the IDENTITY_INSERT option. The following example inserts identity row 2, where iID is defined as the identity column.

Table: tblTest 

iID

strData

1

Jones

3

Smith

/* Enable IDENTITY_INSERT */
SET IDENTITY_INSERT tblTest ON
go

/* Insert the specified identity row using a column list */
INSERT INTO tblTest (iID, strData) values (2, "Lambert")
go

/* Disable IDENTITY_INSERT */
SET IDENTITY_INSERT tblTest OFF
go