Thursday, March 14, 2013

How to insert values to identity column in SQL Server


Consider you have the following Customer table.

CREATE TABLE Customer
(
 Id int identity,
 Name varchar(100),
 Address varchar(200)
)

Now, I am trying to insert a record into Customer table with identity field like as then I will get the error message as shown below.

insert into customer(id,name,address) values(1,'kaushik','jabalpur')

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF.

Allow insert into identity field

You can allow insert to the identity field by setting IDENTITY_INSERT ON for a particular table as shown:

set identity_insert customer on

Disallow insert into identity field

You can also disallow insert to the identity field by setting IDENTITY_INSERT OFF for a particular table as shown:

set identity_insert customer on

Insert Value to Identity field

set identity_insert customer on
insert into customer(id,name,address) values(1,'kaushik','jabalpur')
insert into customer(id,name,address) values(3,'amit','noida')
insert into customer(id,name,address) values(2,'sumit','delhi')

set identity_insert customer off

insert into customer(name,address) values('arun','banglore')

After inserting your own value to identity field don't forget to set IDENTITY_INSERT OFF.

Reseed the Identity field

You can also reseed the identity field value. By doing so identity field values will start with a new defined value.

Suppose you want to reseed the Customer table ID field from 3 then the new records will be inserted with ID 4, 5, 6...and so on.

DBCC checkident (customer, reseed, 3)

insert into customer(name,address) values('geeta','noida')














Post a Comment