Update values in identity column

问题:

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'.

回答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...]

回答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

回答3:

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.

回答4:

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

回答5:

If you want to reenumerate the values of an identity field, because for instance the values have gone mad, just do as follows:

• open table in design mode click on Identity Specification

  mark (is identity) as NO (Your identity field is still a PK)

  close and save design

• open table in edit mode Change the values of your Identity field as desired (just be aware you cannot have duplicate values)

• Close the table and open it again in design mode Replace your identity field (is identity) to Yes.

  Close the table and you are done.