aiDeeva.com

Tag: SQL Documentation

  • How to insert value into IDENTITY column?

    If you will try to insert the value into Identity column you will get the one of the below error.

    Error 1:
    Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table ‘Employee’ when IDENTITY_INSERT is set to OFF.

    Error 2:
    Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON

    Solution:
    Write SET IDENTITY_INSERT table name ON before the insert script and SET IDENTITY_INSERT table name Off after insert script.

    Example,

    use db1

create table Employee
(
	myID int identity(100,1),
	name varchar(20)
)

insert into Employee(name) values('Jugal')

--if i will try to insert the value into Identity column it will fail
insert into Employee(myID,name) values (101,'DJ')

--you can add the data into identiy column by turning on the IDENTITY_INSERT ON

SET IDENTITY_INSERT Employee ON
	insert into Employee(myID,name) values (101,'DJ')
SET IDENTITY_INSERT Employee OFF