How do you UPDATE an identity column in SQL azure? Is it possible?

Question

I have this code:

ALTER TABLE FishSticks
ADD MyNewIdentityColumnId INT IDENTITY(1,1) NOT NULL

SET IDENTITY_INSERT FishSticks ON

UPDATE FishSticks 
SET MyNewIdentityColumnId = MyOldColumnId

However it says:

Cannot update identity column 'MyNewIdentityColumnId'.

I assume this is due to SQL Azure, but I didn't have much success googling. It seems that changing a column to be an identity while preserving data is a huge difficulty.

score 3 · Accepted Answer · answered Feb 10 '12 at 08:55

3

SET IDENTITY_INSERT only works for INSERTS and that error is not specific to SQL Azure. You'll get the same error on SQL Server/Express

to go around the issue, SET IDENTITY_INSERT to ON, reinsert all the column values from the existing row whose identity value you want to replace with a new value, SET IDENTITY_INSERT OFF, then delete the previous row.

answered Feb 10 '12 at 08:55

JuneT

7,840
2
15
14

Another approach, along the same lines, is to copy the table to a new table entirely (let's say FishSticks2), set your MyNewIdentityColumnId to MyOldColumnId during the insert, drop the original table, and rename FishSticks2 to FishSticks. – Herve Roggero Feb 11 '12 at 15:41
It will only work with Azure if you use the full INSERT command, i.e. include all column names. – Aineislis Cole Nov 21 '13 at 14:00

score 0 · Answer 2 · answered Feb 09 '12 at 16:06

0

I found this Possible solution from Herve Roggero who basically gives a way of turning off the ID and then adding the rows, then turning ID back on.

answered Feb 09 '12 at 16:06

psx

4,040
6
30
59

I think his example works because he is inserting data, whereas I am updating data. – NibblyPig Feb 09 '12 at 16:10

How do you UPDATE an identity column in SQL azure? Is it possible?

2 Answers2

Linked