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

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.