Set Identity_Insert Off for All Tables

How to SET IDENTITY_INSERT ON in SQL Server 2008 for multiple tables at once

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.

So before enabling the other one, you should turn of existing if any.

If it is lesser number of tables you can turn on and turn off before and after your operations.

If the table count is huge, you should automate somehow to enable and disable before your operations.

IDENTITY INSERT ON/OFF on an entire database

It isn't totally clear what you want to do, but as the documentation says, only one table in a session can have IDENTITY_INSERT on, therefore you cannot disable it on all tables simultaneously. Your script will have to go table by table, setting it off, doing your inserts for that table only and then setting it on again.

SET IDENTITY_INSERT ON List In Database SQL Server

I think you need this:

SELECT   'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  ON' 
FROM SYS.IDENTITY_COLUMNS 
WHERE OBJECT_NAME(OBJECT_ID) not like 'sqlagent_%'
AND OBJECT_NAME(OBJECT_ID) not like 'queue_messages%'

EDIT:

SELECT 'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  ON;'+'SET IDENTITY_INSERT ' +OBJECT_NAME(OBJECT_ID)+ '  OFF;' 
FROM SYS.IDENTITY_COLUMNS 
WHERE OBJECT_NAME(OBJECT_ID) not like 'sqlagent_%'
AND OBJECT_NAME(OBJECT_ID) not like 'queue_messages%'

How to enable Identity_insert on SQL Server Level

You don't. The documentation confirms this:

SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table.

...

Remarks

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, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Emphasis mine.

If you need to enable it on multiple tables, you must enable it on one table, disable it, then en able it on the next, etc, etc.

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

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

---

Related Topics