Export (0) Print
Expand All
Expand Minimize

Error 8101

SQL Server 2000

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

SET IDENTITY_INSERT

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft