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')