更新SQL Server中的标识列并设置种子起始值 [英] Updating identity column in SQL Server and setting the seed starting value

问题描述

我有一个充满数据的表，并且列之一-TrackingNumber-是一个整数值.我已收到将其更改为自动递增并以1000000开始标识种子的请求.我知道我无法将列更改为具有数据的现有表中的标识列，因此我有两个选择:创建一个全新的表，然后将数据从旧表移到该新表中，或添加新的标识列，并使用旧列中的数据对其进行更新.

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.

问题是我需要保留TrackingNumber列中的所有现有值.我尝试了以下方法:

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'

我在第2步中遇到错误-使用旧列中的值更新新列:无法更新身份列'TrackingNumber2'"

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?

推荐答案

我最终创建了一个新表并在其中移动数据

I ended up creating a new table and moving data in there

这篇关于更新SQL Server中的标识列并设置种子起始值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！