I have a table filled with data and one of the columns - TrackingNumber - is an integer value. I have gotten a request to change it to auto-increment and to have it start the identity seed at 1000000. I know that I cannot alter a column to be an identity column in an existing table with data, so I have two options: either create an entirely new table and then move data from the old table into that new table or add an new identity column and update it with data from the old column.
The problem is that I need to retain all the existing values in column TrackingNumber. I have tried the following:
1) ALTER TABLE [dbo].[Table1]
ADD [TrackingNumber2] [bigint] IDENTITY (1000000, 1) NOT NULL
2) UPDATE [dbo].[Table1]
3) ALTER TABLE [dbo].[Table1]
DROP COLUMN [TrackingNumber]
4) EXEC sp_rename 'Table1.TrackingNumber2', 'TrackingNumber','COLUMN'
I got an error on step 2 - Updating new column with the value from the old column: "Cannot update identity column 'TrackingNumber2'"
Can anyone recommend a workaround?