Insert a value into Identity Column

When columns have the identity property set on them, the value automatically increases on every row, depending on the seed and incrementing value.

So let us create a table with an identity column

CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeName] [char](10) NULL
) ON [PRIMARY]

And now let us add some data in that table:

INSERT TableA(SomeName) VALUES ('A')
INSERT TableA(SomeName) VALUES ('B')
INSERT TableA(SomeName) VALUES ('C')
INSERT TableA(SomeName) VALUES ('D')

If you say

SELECT * FROM TableA ; you will see that the ID column contains value from 1 to 4.

Now let us say that the user goes ahead and deletes ID 2

DELETE FROM TableA where ID = 2 ;

If you now do a SELECT * FROM TableA

There is a gap in between ID 1 and 3. If you try to explicitly add a value to the identity column to fill up the gap using the statement:

INSERT TableA(ID,SomeName) VALUES(2,'R')

You will get an error “Cannot insert explicit value for identity column in table 'TableA' when IDENTITY_INSERT is set to OFF”

In order to insert a value into an Identity column, set Identity to ON. Use the code below to do so:

SET IDENTITY_INSERT TableA ON

INSERT TableA(ID,SomeName) VALUES(2,'R')

SET IDENTITY_INSERT TableA OFF

Setting Identity_Insert to ON removes this check. Once you have inserted the record, set it back to OFF again.