SET IDENTITY_INSERT

SET IDENTITY_INSERT Allows explicit values to be inserted into the identity column of a table.

Syntax:
SET IDENTITY_INSERT [TableName] ON

Example:
CREATE TABLE Mytable(ID int IDENTITY(1,1), City Varchar(50))

INSERT INTO Mytable(ID,City) Values(1,'Bangalore')

When you try inserting values to ID column which is an IDENTITY column, the below error will be shown.

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Mytable' when IDENTITY_INSERT is set to OFF.
If you need to insert values explicitly(Forcefully) you have to use SET IDENTITY_INSERT statement as below.
SET IDENTITY_INSERT Mytable ON
INSERT INTO Mytable(ID,City) Values(1,'Bangalore')
SET IDENTITY_INSERT Mytable OFF
Note
Do not leave a any table "IDENTITY_INSERT ON". This is a bad practice. Because IDENTITY column can allow duplicates unless it is enforced by PRIMARY KEY or UNIQUE KEY constraint. I have posted more details about IDENTITY constraint in this link IDENTITY Column allows duplicate values!

No comments:

Post a Comment