Post reply

Identity Error

  • November 20, 2009 at 4:29 am

    #143093

    I face strange problem..

    My SP is selecting from DB1 and inserting into DB2

    I have no tables which has identity column, neither in DB1 or DB2

    But when I execute the sp it gives me error

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

    Can some one please help . Its little urgent

    Thanks

    Khushbu

  • November 20, 2009 at 4:52 am

    #1082106

    check dependcy on the table whether it is having trigger for insert.

    also if possible script out the table send to us or check the script itself whether it has identity column

    regards

    ramu

  • November 20, 2009 at 4:55 am

    #1082108

    Hi

    Just cross checked and saw that table has identity column.

    But in sp i have written

    SET IDENTITY_INSERT DBNAME.DBO.TBL1 ON

    Then

    insert into ....

    but it still passes me error saying

    cannot perform set operation because table has no identity column

  • November 20, 2009 at 6:15 am

    #1082150

    when you are inserting values in identity column

    use

    SET IDENTITY_INSERT schema_name.table ON

    insert into schema_name.table(col1,col2.....)

    select a,b,c... from table

    SET IDENTITY_INSERT schema_name.table off

    you have to explictly provide list of all columns n the insert statemnt and select statement.

    not to specify database name.

    i hope this will solve the problem

  • November 20, 2009 at 8:32 am

    #1082275

    I think your syntax may be incorrect. If you are inserting into DB2, then the IDENTITY_INSERT needs to be on for that table, not the source table DB1. Your syntax shows DB1 and I think it should be DB2.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply