Error 8101

Severity Level 16

Message Text

An explicit value for the identity column in table '%.*ls' 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 that contains an identity column. However, you did not provide a column list or have SET IDENTITY_INSERT enabled for the specified table.

Action

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

Table: tblTest

iID         strData

1           King

3           Suyama

-- 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, 'Davolio')

GO

-- Disable IDENTITY_INSERT .

SET IDENTITY_INSERT tblTest OFF

GO

See Also