Disable SQL Server Column Identity

First, you do not want to disable the Identity Insert, you want to enable it .

There is no way to do this without "workarounds" (that's right, you're going to have to do a trick).

You can use Insert Into Select (I do not know what this is called truth) to create an identical record with the new Id, and then delete the old one.

See my example, I change the value of Id from 1 to 500 , it's the best way I can think now. >

Note that will not 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

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: IT IS NECESSARY TO SPECIFY THE INSERT COLUMNS, IF IT IS AN INSERT INTO SELECT, IT IS NECESSARY TO SPECIFY THE COLUMNS INSTEAD OF SELECT.

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

Thenclickonthetable'sPK.

Thenchangethefield(It'sindentity)toNo.

Removeyourprimarykey.

Save the change and then do your update.