menu
  • Register

Recent Badges

Popular Question peterlaw
Famous Question mphil
Notable Question peterlaw

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

0 votes
170 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.9k 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
36.1k points

Related questions

0 votes
1 answer 9 views
9 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 2.6k points
0 votes
1 answer 13 views
13 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 32 views
32 views
In sql server, execute the sp_columns command to list all the columns in a table?
Problem: I am little bit confused, which statement is true? In sql server, execute the sp_columns command to list all the columns in a table? True False
asked Feb 19 maddi86 5.4k points
0 votes
1 answer 38 views
38 views
What command can be used to show information about the structure of a table?
Problem : I am very new to SQL.I and my colleague have recently joined SQL project now I have a table on my SQL Server and I want to export the structure of the table so that I can send it to my colleague. Which SQL command should one fire to get the required table structure? Please note that I don't have any access to the SQL Server Management Studio.
asked Jan 27 jwilliam 3.9k points
Dark theme