What to do if identity_insert is on?
How can I insert an ID into an identity column in SQL Server? I'll make a special item into my table and I will quickly detect that item by the ID. So because all other IDs are bigger or equal to one, I will give that special item the ID zero.
The problem is now, when I've created that table I've placed the identity of the column ID on true. If I insert now the special item with code below, I've got next error:
INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');
Error:
Cannot insert explicit value for identity column in table MyTable when IDENTITY_INSERT is set to OFF.
How can I change this table, column or property so I can create a record with ID zero?
If identity_insert is on -
Explicit identity insert requires IDENTITY_INSERT property set to ON.
SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into
-- the identity column of a table.
GO
INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');
GO
SET IDENTITY_INSERT MyTable OFF -- Statement revert granted permission
GO