Disable SQL Server Column Identity

Question

How do I disable the property identity of a column in the SQL Server?

I’ve tried to:

SET IDENTITY_INSERT ON/OFF

But it didn’t work. I don’t know if it’s only for insertion, but I need to do an update.

Answer 1

Ex.1:

SET IDENTITY_INSERT masterTbl ON  --Desabilita o IDENTITY

INSERT INTO masterTbl (id, name) VALUES (1, 'MNO') --Consegue inserir

SET IDENTITY_INSERT masterTbl OFF  --Habilita o IDENTITY

Ex.2:

SET IDENTITY_INSERT produto ON --Desabilita o IDENTITY

insert into produto (Codigo, Nome) --Consegue inserir
SELECT Codigo, Nome
from produto2
where codigo in (46296,46298,46301)

SET IDENTITY_INSERT produto OFF --Habilita o IDENTITY

OBS: INSERT COLUMNS NEED TO BE SPECIFIED IF IT IS AN INSERT INTO SELECT, BOTH INSERT AND SELECT COLUMNS NEED TO BE SPECIFIED.

Answer 2

First of all, you don’t want disable Identity Insert, you want enable him.

There’s no way to do this without "workarounds" (That’s right, you’ll have to do a gambiarra).

You can use a Insert Into Select (i don’t know what it’s called true) to create an identical record with the new id, and then delete the old one.

Take my example, I change the value of Id of 1 for 500, is the best way I can think now.

Note that this won’t work if there are FK’s with reference to this id being exchanged.

Set Identity_insert Tabela On
Go

Insert Into Tabela (Id, Nome, Etc, Etc2) 
     Select 500, (Nome, Etc, Etc2) -- 500 é o novo Id
     From Tabela Where Id = 1 -- 1 é o Id antigo

Delete Tabela Where Countryid = 1 

Set Identity_insert Geocountry Off
Go

Answer 3

You need to temporarily authorize the insertion of identity values, to do so in SQL Server Management Studio right-click on the table and click Design as shown below.

After that click on the PK of the table.

then change the field (Is indentity) to No.

Remove your primary key.

Save the change and then update.