Setting the
IDENTITY_INSERT
property of the table to ON will not make any difference as this only affects new records and not existing ones.
If you really need to change the values of an identity column, this can be accomplished in 2 steps. The first step is to INSERT new records containing the same information of the existing records whose identity column needs to be updated assigning the new IDs for these new records. The second step is deleting the original records.
but then that's a risk because you may not want to loose your data, and also if there is any referential integrity like Foreign keys to other tables. Then you also need to remove the entries from that tables.
So quite a risk. but if you are doing it in a simple purpose then try this.
set identity_insert YourTable ON
set identity_insert YourTable OFF