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