How to insert values into identity column?

Identity columns are used to automatically assign new incremented value to identity column when

new record inserted based on previous value in SQL server.

I will explain with example. First create table like this

CREATE TABLE UserDtls

 (

 UserId int PRIMARY KEY IDENTITY,

 UserName varchar(120),

 Qualification varchar(50)

 )

we create UserDtls, now insert the data

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Milan','B.E')

to run this query we will get error like as shown below

Cannot insert explicit value for identity column in table 'UserDtls' when IDENTITY_INSERT is set to OFF.

 Based on above error we don't insert new values when IDENTITY_INSERT is OFF. To solve this problem we need to Set is ON and write the code like this.

SET IDENTITY_INSERT UserDtls ON

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Milan','B.E')

INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(2,'Chirag','Civil Eng.')

SET IDENTITY_INSERT UserDtls OFF