menu
  • Register

cannot insert explicit value for identity column in table when identity_insert is set to off.

0 votes
260 views

Problem :

I am facing following error while executing script
cannot insert explicit value for identity column in table when identity_insert is set to off.
●6 ●5 ●3
6,930 points

1 Answer

0 votes

Solution :

I guess you are inserting values for an identity column.

You can easily turn on identity insert on the table like shown below to specify your own identity values.

e.g.
SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
/*Please note the column list is REQUIRED here not optional*/
            (OperationID,
             OpDescription,
             FilterID)
VALUES      (20,
             'Hierachy Update',
             1)

SET IDENTITY_INSERT Table1 OFF
●9 ●7 ●4
38,600 points

Related questions

0 votes
1 answer 19 views
19 views
Cannot define primary key constraint on nullable column in table.
Problem: I am able to create the table named employee which contains the column empid which is a computed column and empid column is the primary key also. It is the self referencing table and the column managerid references back to itself. But now if I try to save ... error my table is still working perfectly. I am trying to work in a table graphically not coding every time I make the change.
asked Jul 4 Raphael Pacheco 4.9k points
0 votes
1 answer 15 views
15 views
Can only be specified when a column list is used and identity_insert is on.
Problem : I want to do below query INSERT INTO dbo.tbl_A_archive  SELECT * FROM SERVERxyz.DB.dbo.tbl_A However even after I ran below code set identity_insert dbo.tbl_A_archive on I am facing below error message “Can only be specified when a column list is used and IDENTITY_INSERT ... a huge number of columns. I can not type all of the columns out manually. How can I get this to be fixed?
asked Jan 29 jwilliam 3.9k points
0 votes
1 answer 4 views
4 views
How to insert multiple rows in sql at a time
asked Oct 8 kalyani456 280 points
Dark theme