How to Turn Identity_Insert on and Off Using SQL Server 2008

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.

IDENTITY_INSERT is set to OFF - How to turn it ON?

Should you instead be setting the identity insert to on within the stored procedure? It looks like you're setting it to on only when changing the stored procedure, not when actually calling it. Try:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF
GO

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.

Setting Identity to on or off in SQL server

All the line you've given does is to disable the identity so that you can insert specific values into your identity column - usually this is needed for one-offs such as moving data around. The identity is still there on the column, its just not being acted upon. Conceptually this is similar to the difference between disabling and removing triggers.

To remove the identity from the column entirely is harder. The question covers it, but the basic idea is that you have to create a new column, copy the data over, then remove the identity column.

SQL Server 2008 IDENTITY_INSERT is ON error on Insert

You need to explicitly mention column names in target table while inserting .
Replace Col1,Col2 etc with column list of CP.[dbo].[LOCALE_CODE] table

SET IDENTITY_INSERT CP.dbo.LOCALE_CODE ON

insert into CP.[dbo].[LOCALE_CODE] (Col1 ,Col2 , Col3) 
select * from PP.dbo.LOCALE_CODE 

You can use * in SELECT of source table PP.dbo.LOCALE_CODE

IDENTITY_INSERT ON not working - SQL Server 2008 R2

You have to specify all the column names when inserting with IDENTITY INSERT ON when using INSERT INTO

INSERT INTO  [DB1].[dbo].[MY_TABLE](TabelID,Field1,Field2,Field3...)
SELECT * FROM  [DB2].[dbo].[MY_TABLE]

In case you did not know there is a nifty little trick in ssms. If select a table and expand its' nodes you ctrl-c copy on the Columns node and that will place a comma-delimited list of the field names on your clipboards text buffer.

Turn on IDENTITY_INSERT in Entity Framework, SQL Server 2008?

When using an identity column, you need to make sure you have set this appropriately in your EDMX (Entity Framework) model. Your column should look like this:

Is your column set to be StoreGeneratedPattern = Identity and Entity Key = True ?? If not - try that!

That's the default that should be mapped automatically, when that situation existed at the time you created the EDMX model. If you changed your column in SQL Server after you've created the model and you didn't update the model, then you might have a discrepancy that could lead to such errors.

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.

---

Related Topics