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'.
Update values in identity column
Viewed 105195
5 Answers
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
Related