override identity column

Question

How do I override the identity column in MSSQL? I tried

set identity_insert GeoCountry on
update GeoCountry
set CountryID = 18
where CountryID = 250

but I get back a

 Line 2: Cannot update identity column 'CountryID'.

Answer 1

You are trying to perform an update, not inserting new rows.

In order to do that, you will need to set identity_insert ON and copy the row you want to update to a new row with the new ID value, then delete the old row (assuming no FK is referencing it)

Something along the lines of:

set identity_insert GeoCountry on
go

insert into GeoCountry (all columns except IDentity column) 
     select 18, (all columns except IDentity column)
     from GeoCountry where CountryID = 250 

-- Delete will only work if no referencing FK's
delete GeoCountry where CountryID = 250

set identity_insert GeoCountry off
go

[Given that you are trying to update it, that would suggest it is still in use (i.e. by refrenecing FK's) and that makes things more complicated...]