Update values in identity column
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
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...]
If you are trying to update an identity column here is one possible approach:
- In SQL Server Management Studio, open the table in design view, disable "Identity Specification > Is Identity" on the column
- Perform updates
- Enable "Identity Specification > Is Identity" on the column
Do a SELECT IDENT_CURRENT('<table name>')
to see if it returns the highest id that is currently present in the table.
You could also do this in one statement using delete into, this has the benefit of eliminating any error copying/moving the row data, for example
set identity_insert [dbo].[MyTableName] on
delete from [dbo].[MyTableName]
output
<new-id-value-here>,
[deleted].[Col1],
[deleted].[Col2],
[deleted].[Col3],
into
[dbo].[MyTableName] (
[IdColumnName],
[Col1],
[Col2],
[Col3])
where
[IdColumnName]=<old-id-value-here>
set identity_insert [dbo].[MyTableName] off