CODEWITHSUNDEEP
sql-serverdatabasedatabase-administration
mohamed nasr
mohamed nasr

Reputation: 23

How to enable Identity_insert on SQL Server Level

In the article https://support.microsoft.com/en-us/help/4568653/fix-insert-exec-doesn-t-work-when-you-insert-row-containing-explicit-i it said :

INSERT EXEC doesn't work when you insert row containing explicit identity value into table with IDENTITY column and IDENTITY_INSERT is OFF by default in SQL Server

This means IDENTITY_INSERT can be set OFF by default in SQL Server and in the same manner ON. Does someone know how to achieve this?

Upvotes: 2

Views: 7897

Answers (2)

Thom A
Thom A

Reputation: 95561

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.

Upvotes: 2

Ehssan
Ehssan

Reputation: 759

You have to enable it for any table that you want to insert into like this:

SET IDENTITY_INSERT [dbo].[tablename] ON

After you have completed the identity insert, you have to disable it

SET IDENTITY_INSERT [dbo].[tablename] OFF

After having disabled it you can enable it for another table

See here for documentation

Upvotes: 5

Related Questions