How to insert value in a Identity field in Sqlserver.

Suppose there is only one column and that is identity field in a table and your task is to insert value in that column, if you try to insert  value like we normally do then it will throw an exception.

create table tIdentity(id int identity(1,1))
insert into tIdentity(id) select 1 — or insert into tIdentity(id) values(1)
select * from tIdentity
drop table tIdentity


But there is a way to do the same task using “identity_insert”. Before insert query we’ll have to set it to “On” and once we are done then set it to “Off”.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s