SQL Server Performance

identitiy insert

Discussion in 'General Developer Questions' started by Chappy, Aug 14, 2003.

  1. Chappy New Member

    Im having trouble enabling identity insert in a stored procedure when transferring data between two databases



    -----------------------------------------------------------------------
    -- Procedure: dbo.deadline_performSubstep_NVDDictionaryOption
    --
    -- Deadlines the NVDDictionaryOption tables in their entirety
    --
    -- NVDDictionaryOption-> NVDDictionaryOption
    --
    -- 14/08/2003.PAC - Initial Version
    -----------------------------------------------------------------------
    CREATE PROCEDURE dbo.deadline_performSubstep_NVDDictionaryOption
    AS
    BEGIN
    DECLARE @ErrorCode int

    truncate table DEAD_CAR.dbo.NVDDictionaryOption
    SET IDENTITY_INSERT DEAD_CAR.dbo.NVDDictionaryOption ON

    insert into
    DEAD_CAR.dbo.NVDDictionaryOption
    select
    DO_OptionCode,
    DO_ManLevel,
    DO_CatCode,
    DO_Description,
    DO_LongDescription,
    DO_NonSpecificCostOption
    from
    CAR.dbo.NVDDictionaryOption

    SET IDENTITY_INSERT DEAD_CAR.dbo.NVDDictionaryOption OFF
    RETURN @@ERROR
    END
    GO

    I get the error



    Error 8101: An explicit value for the identity column in table 'DEAD_CAR.dbo.NVDDictionaryOption' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Can anyone advise a solution ?
  2. gaurav_bindlish New Member

    BOL states -

    At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

    Do you think this is the problem?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. satya Moderator

  4. Chappy New Member

    Thanks guys, but neither of these appear to be the problem. As far as I know no other table has identity_insert on as the error im getting does not indicate this to be the case.<br /><br />I thought maybe the use of a second database was confusing the parser, so I duplicated CAR.NVDDictionaryOption to CAR.NVDDictionaryOption2 and tried the transfer within a single database.<br /><br />Again I get the same error.<br />--<br />An explicit value for the identity column in table 'NVDDictionaryOption2' can only be specified when a column list is used and IDENTITY_INSERT is ON.<br /><br /><pre><br />truncate table NVDDictionaryOption2<br />SET IDENTITY_INSERT NVDDictionaryOption2 ON <br />insert into <br />NVDDictionaryOption2<br />select <br />"DO_OptionCode"=DO_OptionCode,<br />"DO_ManLevel"=DO_ManLevel,<br />"DO_CatCode"=DO_CatCode,<br />"DO_Description"=DO_Description,<br />"DO_LongDescription"=DO_LongDescription,<br />"DO_NonSpecificCostOption"=DO_NonSpecificCostOption<br />from<br />NVDDictionaryOption<br />SET IDENTITY_INSERT NVDDictionaryOption2 OFF<br /></pre><br /><br />this fails too, even outside of a stored procedure <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  5. Chappy New Member

    Ah.. it seems the clue was in the error message. It was asking for a column list, which I assumed it meant my select. It didnt occur to me what it wanted the inserted columns explicitly stated.



    truncate table NVDDictionaryOption2
    SET IDENTITY_INSERT NVDDictionaryOption2 ON

    insert into
    NVDDictionaryOption2
    (DO_OptionCode, DO_ManLevel, DO_CatCode, DO_Description, DO_LongDescription, DO_NonSpecificCostOption)
    select
    DO_OptionCode,
    DO_ManLevel,
    DO_CatCode,
    DO_Description,
    DO_LongDescription,
    DO_NonSpecificCostOption
    from
    NVDDictionaryOption

    SET IDENTITY_INSERT NVDDictionaryOption2 OFF

    Works a treat. Thanks
  6. ykchakri New Member

    As the error suggests you have to use the column list in the insert command when you are trying to explicitly insert values into an Identity column.

    For example in your case, you have to modify your statement as below:

    insert into
    DEAD_CAR.dbo.NVDDictionaryOption (DO_OptionCode,
    DO_ManLevel,
    DO_CatCode,
    DO_Description,
    DO_LongDescription,
    DO_NonSpecificCostOption)
    select
    DO_OptionCode,
    DO_ManLevel,
    DO_CatCode,
    DO_Description,
    DO_LongDescription,
    DO_NonSpecificCostOption
    from
    CAR.dbo.NVDDictionaryOption

    I am assuming here that you have same column names in both tables.
  7. gaurav_bindlish New Member

    I agree with ykchakri. Try specifying the name of the columns in the insert statement and then execute the query.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. ykchakri New Member

    Sorry Chappy,<br /><br />I think I was bit late in posting the answer <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  9. Chappy New Member

    Thats ok. Thanks for taking the time anyway <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page