36

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

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
lowerkey
  • 8,105
  • 17
  • 68
  • 102

5 Answers5

69

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

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 1
    It worked for me too but the part in brackets was confusing in first place. Therfore to be a bit more precise: insert into GeoCountry (column1, column2, columnN) select 18, column2, columnN from GeoCountry where CountryID = 250 – Laminar Sep 21 '18 at 07:22
12

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

Gary J.
  • 137
  • 1
  • 2
  • 2
    +1 IMO this is the correct answer. When posed with the question "How do I update the identity value" it is an important detail that you cannot in fact UPDATE the value but must DELETE and re-INSERT. The accepted answer fails in this respect. – alan Sep 03 '15 at 20:40
10

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • This method is the nicer way to go if you have foreign keys with cascading updates because you get the updates for "free". – Jacob Jul 02 '14 at 22:59
5

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
Ismail Hawayel
  • 2,167
  • 18
  • 16
0

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.

Ramon Baiges
  • 11
  • 2