Error 544

Severity Level 16

Message Text

Cannot insert explicit value for identity column in table '%.*ls' when IDENTITY_INSERT is set to OFF.

Explanation

This error occurs when you have attempted to insert a row that contains a specific identity value into a table that contains an identity column. However, SET IDENTITY_INSERT is not enabled for the specified table.

Action

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

USE pubs

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'tblTest')

    DROP TABLE tblTest

GO

CREATE TABLE tblTest

( iID    int IDENTITY(1, 1),

  strData nvarchar(15)

)

GO

INSERT INTO tblTest (strData) VALUES (N'Leverling')

INSERT INTO tblTest (strData) VALUES (N'Davolio')

GO

SET IDENTITY_INSERT tblTest ON

GO

-- Insert the specified identity row using a column list.

INSERT INTO tblTest (iID, strData) VALUES (5, N'Callahan')

GO

-- Display the rows in tblTest to see identity values.

SELECT *

FROM tblTest

-- Disable IDENTITY_INSERT.

SET IDENTITY_INSERT tblTest OFF

GO

See Also