Update values in 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 including 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 referencing FK's) and that makes things more complicated...]

Answer 2

You cannot update the Identity Column in SQL Server. You have to delete the original record, then Insert the record with the Identity value because there is no support for updating an identity value.

set Identity_Insert [ColumnName] On Insert identity and additional information previously stored in that record set Identity_Insert [ColumnName] Off