添加AUTO_INCREMENT

Question

我有一个表，其中我将两个表合并为一个表。其中一个表有一个ID (主键)。

现在我得到了一个合并表，其中一些ID是0。

我现在尝试用AUTO_INCREMENT恢复和填充0，这样我就可以得到一个具有唯一编号的表(而不会丢失已经存在的表)

有人有上帝的解决方案吗？

Answer 1

首先，表中有一堆0意味着两个额外的问题：

1. "ID“列不是主键或其上没有唯一索引；这意味着在该列(很可能)不再是IDENTITY.

时插入了重复项

首先，您需要将新值放入其中。这可以通过可更新的CTE、ROW_NUMBER和窗口MAX来完成。

首先是一些样本数据：

CREATE TABLE dbo.TestTable (ID int NOT NULL);

INSERT INTO dbo.TestTable (ID)
VALUES(1),(2),(3),(0),(0),(0);

现在使用0对行执行UPDATE操作

WITH RNs AS(
    SELECT ID,
           ROW_NUMBER() OVER (PARTITION BY CASE ID WHEN 0 THEN 0 ELSE 1 END ORDER BY (SELECT NULL)) + --If you have a way of determining the order, change the ORDER BY
           MAX(ID) OVER () AS [NewID]
    FROM dbo.TestTable)
UPDATE RNs
SET ID = [NewID]
WHERE ID = 0;

现在，我们(可能)需要修复该表，并在其中获取IDENTITY列。您不能将列更改为IDENTITY，因此我们需要创建一个新的列，并确保它遵循现有ID的值。

因此，首先，我们需要向表中添加一个CLUSTERED索引，以便新的IDENTITY将使用该索引来生成其值：

ALTER TABLE dbo.TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ID);

现在我们可以添加新的IDENTITY列：

ALTER TABLE dbo.TestTable ADD IdentityID int IDENTITY NOT NULL;

然后，我们需要DROP我们刚刚创建的主键，然后是旧的列：

ALTER TABLE dbo.TestTable DROP CONSTRAINT PK_TestTable ;
ALTER TABLE dbo.TestTable DROP COLUMN ID;

最后，我们可以重命名新列，然后重新创建主键：

EXEC sys.sp_rename N'dbo.TestTable.IdentityID','ID','COLUMN';
GO
ALTER TABLE dbo.TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ID);