Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

sql sql-server sybase
Jaison picture Jaison · Aug 26, 2009 · Viewed 815.3k times · Source

I have the below error when I execute the following script. What is the error about, and how it can be resolved?

Insert table(OperationID,OpDescription,FilterID)
values (20,'Hierachy Update',1)

Error:

Server: Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF.

Answer

pjp picture pjp · Aug 26, 2009

You're inserting values for OperationId that is an identity column.

You can turn on identity insert on the table like this so that you can specify your own identity values.

SET IDENTITY_INSERT Table1 ON

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

SET IDENTITY_INSERT Table1 OFF

Related questions

Difference between drop table and truncate table?

I have some tables that I build as a part of my report rollup. I don't need them afterwards at all. Someone mentioned to truncate them as it would be faster.

Read More
Execute stored proc with OPENQUERY

I have SQL Server 2008 with a linked Sybase server and I am trying to execute a stored procedure on the Sybase server using OPENQUERY. If I have a stored proc that doesn't take parameters it succeeds fine. If I have …

Read More
Inserting Multiple Rows in Sybase ASE

(Similar question related to SQL Server : SO Link) I know in Sql Server 2008 or above, you can insert multiple rows doing the following: INSERT INTO MyTable (Name, ID) VALUES ('First',1), ('Second',2), ('Third',3) However, it seems like this syntax DOES NOT …

Read More