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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook