To
insert value in identity column I will explain with one example for that first
create one sample table like as shown below
CREATE TABLE UserDtls
(
UserId int PRIMARY KEY IDENTITY,
UserName varchar(120),
Qualification varchar(50)
)
|
Once
we create UserDtls insert data like as shown below
INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Brijesh','B.Tech')
|
Whenever
we run above query we will get error message like as shown below
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in
table 'UserDtls' when IDENTITY_INSERT is set to OFF.
|
Based
on above error message we can realize that identity columns won't allow to
insert new values when IDENTITY_INSERT is
OFF
.To solve this problem we need to set is ON for that we need to write the code
like as shown below
SET IDENTITY_INSERT UserDtls ON
INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(1,'Brijesh','B.Tech')
INSERT INTO UserDtls(UserId,UserName,Qualification) VALUES(2,'Rohini','MSC')
SET IDENTITY_INSERT UserDtls OFF
|
Once
we run above query our Output will be like this
---------------------------------------
(1 row(s) affected)
(1 row(s) affected)
|
In this way we can insert values to identity columns in
SQL Server.