Updating identity column in SQL Server and setting the seed starting value

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]
    SET [TrackingNumber2]=[TrackingNumber]

    3) ALTER TABLE [dbo].[Table1]
    DROP COLUMN [TrackingNumber] 

    4) EXEC sp_rename 'Table1.TrackingNumber2', 'TrackingNumber','COLUMN'

Open in new window

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?
LVL 35
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You need to turn the IDENTITY_INSERT option to on before the update, like this:
UPDATE [dbo].[Table1]
    SET [TrackingNumber2]=[TrackingNumber]

Open in new window

Vikas GargAssociate Principal EngineerCommented:

You can use CTE and row_number function for the same

;With CTE as
(select *,row_number()over(order by TrackingNumber) rn + 1000000 from Table)

update cte set TrackingNumber2 = RN
Jagdish DevakuSr DB ArchitectCommented:

Please try the following:

1. Create a new table "temp" with the same structure of "Table1" but set the "TrackingNumber" to identity on creation

2. Insert the data from "table1" to "temp"
      SET IDENTITY_INSERT [dbo].temp ON
      INSERT INTO temp
      SELECT * FROM table1
      SET IDENTITY_INSERT [dbo].temp OFF

3. Drop the "table1"

4. Rename table "temp" to "table1"

Hope this works.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jagdish DevakuSr DB ArchitectCommented:
For the above steps make sure that you generate drop and create scripts of key (foreign keys, triggers).

1. first execute the Drop script for keys and triggers.

2. Follow the steps in the above comment.

3. Now execute the create script for keys and triggers.

YZlatAuthor Commented:
JagdishDevaku, I tried your suggestion and got en error:

An explicit value for the identity column in table 'dbo.temp' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I tried listing all the columns and it worked!
Jagdish DevakuSr DB ArchitectCommented:
Great... Any other issues are you facing.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.