Identity is a property that can be set on a column. When an identity is enabled the column will be populated automatically by system and it will not allow user to enter value explicitly, in this post let’s see how to insert values into a identity column.
Let’s create table to explain in details.
CREATE TABLE dbo.ErrorLog (LogId INT IDENTITY(1,1), LogMsg VARCHAR(MAX))
INSERT INTO ErrorLog (LogMsg) VALUES('a') GO 10
SELECT * FROM dbo.ErrorLog
The test table is created and populated with some sample records. Let’s insert a record into the table and specify value for the LogId column explicitly.
INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(0,'b')
As expected we get the error message Cannot insert explicit value for identity column in table ‘ErrorLog’ when IDENTITY_INSERT is set to OFF. There are situations where we need to enter value into the Identity enabled column. To do that we must turn on the IDENTITY_INSERT property as in below example. The identity_insert property must be turned off after the insert otherwise it always expect you to specify value for the identity enabled column with in the session.
SET IDENTITY_INSERT dbo.ErrorLog ON INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(20,'b') SET IDENTITY_INSERT dbo.ErrorLog OFF