Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Unanswered: IDENTITY_INSERT persistency

    Hi all, quick question:

    Is the IDENTITY_INSERT persistent, or only for a single transaction. I'm of course trying to insert into a database that has Idenity, and was wondering if I can just have a stored procedure run at startup to loop through all tables with identity fields and set IDENTITY_INSERT to on.

    If not, I'll just have code up scripts to restructure the tables.

    Thanks,

    Cooper

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The IDENTITY_INSERT setting is persistant how ever what you want to do won't work. From BOL....

    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.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •