asked on

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

    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?

chaau

You need to turn the IDENTITY_INSERT option to on before the update, like this:

SET IDENTITY_INSERT Table1 ON
UPDATE [dbo].[Table1]
    SET [TrackingNumber2]=[TrackingNumber]
SET IDENTITY_INSERT Table1 OFF

Open in new window

Vikas Garg

Hello,

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

ASKER CERTIFIED SOLUTION

Jagdish Devaku

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagdish Devaku

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.

Thanks.

YZlat

ASKER

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 Devaku

Great... Any other issues are you facing.