將自動增量添加到現有 PK

我在另一個數據庫中已經存在的數據庫中創建了一個表。它最初是用舊的數據庫數據填充的。表的 P​​K 必須接收那些記錄上已經存在的值，所以它不能是自動增量的。

現在我需要新表將其 PK 作為自動增量。但是在 PK 已經存在並且有數據之後，我該怎麼做呢？

我理解你的問題的方式是你有一個現有的表，其中有一列到目前為止已經填充了手動值，現在你想要 (1) 使這個列成為一個IDENTITY列，並且 (2) 確保IDENTITY開始從現有行中的最新值。

首先，一些測試數據可以使用：

CREATE TABLE dbo.ident_test (
   id    int NOT NULL,
   xyz   varchar(10) NOT NULL,
   CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);

INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
      (2, 'test'),
      (5, 'test'),
      (6, 'test'),
      (10, 'test'),
      (18, 'test'),
      (19, 'test'),
      (20, 'test');

目標是使表的主鍵列 ,id成為IDENTITY從 21 開始插入的下一條記錄的列。對於此範例，該列xyz表示該表的所有其他列。

在你做任何事情之前，請閱讀這篇文章底部的警告。

首先，萬一出現問題：

BEGIN TRANSACTION;

現在，讓我們添加一個臨時工作列，id_temp並將該列設置為現有id列的值：

ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;

接下來，我們需要刪除現有id列（您不能只是“添加”IDENTITY到現有列，您必須將列創建為IDENTITY）。主鍵也必須去，因為列依賴它。

ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;

…並再次添加該列，這次作為IDENTITY，以及主鍵：

ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);

這就是有趣的地方。您可以IDENTITY_INSERT在表上啟用，這意味著您可以IDENTITY在插入新行時手動定義列的值（但不更新現有行）。

SET IDENTITY_INSERT dbo.ident_test ON;

使用該設置，DELETE表中的所有行，但您要刪除的行都在OUTPUT同一個表中 - 但列具有特定值id（來自備份列）。

DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);

一次，完成，IDENTITY_INSERT再次關閉。

SET IDENTITY_INSERT dbo.ident_test OFF;

刪除我們添加的臨時列：

ALTER TABLE dbo.ident_test DROP COLUMN id_temp;

最後，重新設置列的種子IDENTITY，這樣下一條記錄id將在列中現有的最高數字之後恢復id：

DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)

檢查範例表，最高id數字為 20。

SELECT * FROM dbo.ident_test;

添加另一行並檢查其新行IDENTITY：

INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;

在範例中，新行將具有id=21. 最後，如果您滿意，請送出事務：

COMMIT TRANSACTION;

重要的

這不是一個微不足道的操作，它帶有很多你應該注意的風險。

• 在專用的測試環境中執行此操作。有備份。:)
• 我喜歡使用它，BEGIN/COMMIT TRANSACTION因為它可以防止其他程序在您更改表格時弄亂表格，並且如果出現問題，它可以讓您回滾所有內容。但是，在您送出事務之前嘗試訪問您的表的任何其他程序最終都會等待。如果您有一張大桌子和/或您在生產環境中，這可能會非常糟糕。
• OUTPUT .. INTO如果您的目標表具有外鍵約束或我不記得的許多其他功能中的任何一個，則將無法使用。您可以改為將數據解除安裝到臨時表中，然後將其重新插入原始表中。您也許可以使用分區切換（即使您不使用分區）。
• 一個接一個地執行這些語句，而不是作為批處理或在儲存過程中。
• 嘗試考慮可能取決於id您要刪除和重新創建的列的其他事情。必須刪除並重新創建任何索引（就像我們對主鍵所做的那樣）。請記住為您需要預先重新創建的每個索引和約束編寫腳本。
• 禁用表上的任何INSERT和DELETE触發器。

如果重新創建表是一個選項：

如果您可以選擇重新創建表，那麼一切都會簡單得多：

• 創建空表，id列為IDENTITY,
• IDENTITY_INSERT ON為餐桌設置，
• 填充表格，
• 設置IDENTITY_INSERT OFF, 和
• 重新設定身份。

引用自：https://dba.stackexchange.com/questions/128433