menu
  • Register

Want to solve programming problems and get paid for it? If that sounds interesting to you then contact us.

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

0 votes
319 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

Please log in or register to answer this question.

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 29 views
29 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, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 18 views
18 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, 2020 jwilliam 3.9k points
0 votes
1 answer 11 views
11 views
How to insert multiple rows in sql at a time
asked Oct 8, 2020 kalyani456 280 points
0 votes
1 answer 17 views
17 views
if you assign a correlation name to one table in a join,
asked Sep 13, 2020 Daniel Anderson 4k points
0 votes
1 answer 154 views
154 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, 2020 maddi86 5.4k points
0 votes
2 answers 268 views
268 views
cannot create an instance of ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)".
Problem : I am trying to export from my Table data into Excel through T-SQL query but getting bellow error cannot create an instance of ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)".
asked Nov 7, 2019 peterlaw 6.9k points
0 votes
1 answer 53 views
53 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, 2020 jwilliam 3.9k points
0 votes
1 answer 6 views
6 views
I received an error message when converting data type varchar to numeric
I received an error message when converting data type varchar to numeric I imported data into VARCHAR column to check correct character types before moving into the destination table. I ran across some decimal values they could not CAST or CONVERT even though they ... values. I received the message "Error converting data type varchar to numeric.” How can I load these problems values?
asked Nov 24, 2020 miki 1.9k points
0 votes
1 answer 454 views
454 views
Conversion failed when converting character string to smalldatetime data type
Problem : I have the table storing starttime and endtime. I am facing below error: “Conversion failed when converting character string to smalldatetime data type.” While selecting the below query using that table. select CAST(substring(CONVERT(varchar,convert(smalldatetime,o. ... face any error. But, when I select it from a table I got the error. Can somebody help me with this?
asked Jan 20, 2020 jwilliam 3.9k points
Dark theme