Setting Identity to on or Off in SQL Server

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

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.

When should I use SET IDENTITY_INSERT tablename OFF function?

From the documentation:

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

So, if you are in a batch where you want to override the auto-generated identity values on two different tables, you would set the first one to OFF before setting the second one to ON.

(Also, like a lot of things, if you change something from the default, it's never a bad idea to change it back.)

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.

Alter table to set the IDENTITY to off

Once the identity column is set you cannot remove it or you cannot set it to OFF.

You probably have to drop the column by first copying the data into some other column(which does not have the identity). So it would be like add a new column
to your table and copy the values of your existing identity column to it. Then drop the old column(having identity) and finally rename the new column to the old column name.

How to update Identity Column in SQL Server?

You can not update identity column.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

Although there are some alternatives to achieve a similar kind of requirement.

• When Identity column value needs to be updated for new records

Use DBCC CHECKIDENT which checks the current identity value for the table and if it's needed, changes the identity value.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• When Identity column value needs to be updated for existing records

Use IDENTITY_INSERT which allows explicit values to be inserted into the identity column of a table.

SET IDENTITY_INSERT YourTable {ON|OFF}

Example:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

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%'

---

Related Topics