4

This question already has an answer here:

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 ID's 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 place 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?

marked as duplicate by Philᵀᴹ, Max Vernon, Tom V, Paul White sql-server Jan 12 '16 at 19:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

12

Explicit identity insert require 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
4

USE SET IDENTITY_INSERT TABLEnAME ON

Remember to turn off identity and that only one table can have identity_insert On at one time

Not the answer you're looking for? Browse other questions tagged or ask your own question.