如何將遷移數據複製到具有標識列的新表，同時保留 FK 關係？

我想將數據從一個數據庫遷移到另一個。表模式完全相同：

CREATE TABLE Customers(
   [Id] INT NOT NULL PRIMARY KEY IDENTITY,
   (some other columns ......)
);

CREATE TABLE Orders(
   [Id] INT NOT NULL PRIMARY KEY IDENTITY,
   [CustomerId] INT NOT NULL,
   (some other columns ......),
   CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
)

兩個數據庫有不同的數據，所以同一張表的新標識鍵在兩個數據庫中會不同。這不是問題；我的目標是將新數據附加到現有數據中，而不是完成替換整個表的所有數據。但是我想維護插入數據的所有父子關係。

如果我使用 SSMS 的“生成腳本”功能，腳本將嘗試使用相同的 ID 插入，這會與目標數據庫中的現有數據發生衝突。如何僅使用數據庫腳本複制數據？

我希望目的地的標識列從其最後一個值正常繼續。

Customers沒有任何其他UNIQUE NOT NULL約束。在其他列中存在重複數據是可以的（我在這裡使用Customers和Orders作為範例，所以我不必解釋整個故事）。問題是關於任何一對 N 的關係。

這是一種可以輕鬆擴展到三個相關表的方法。

使用 MERGE 將數據插入到復製表中，以便您可以將新舊 IDENTITY 值輸出到控製表中，並將它們用於相關表映射。

實際的答案只是兩個創建表語句和三個合併。其餘的是樣本數據設置和拆卸。

USE tempdb;

--## Create test tables ##--

CREATE TABLE Customers(
   [Id] INT NOT NULL PRIMARY KEY IdENTITY,
   [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders(
   [Id] INT NOT NULL PRIMARY KEY IdENTITY,
   [CustomerId] INT NOT NULL,
   [OrderDate] DATE NOT NULL,
   CONSTRAINT [FK_Customers_Orders] FOREIGN KEY ([CustomerId]) REFERENCES [Customers]([Id])
);

CREATE TABLE OrderItems(
   [Id] INT NOT NULL PRIMARY KEY IdENTITY,
   [OrderId] INT NOT NULL,
   [ItemId] INT NOT NULL,
   CONSTRAINT [FK_Orders_OrderItems] FOREIGN KEY ([OrderId]) REFERENCES [Orders]([Id])
);

CREATE TABLE Customers2(
   [Id] INT NOT NULL PRIMARY KEY IdENTITY,
   [Name] NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders2(
   [Id] INT NOT NULL PRIMARY KEY IdENTITY,
   [CustomerId] INT NOT NULL,
   [OrderDate] DATE NOT NULL,
   CONSTRAINT [FK_Customers2_Orders2] FOREIGN KEY ([CustomerId]) REFERENCES [Customers2]([Id])
);

CREATE TABLE OrderItems2(
   [Id] INT NOT NULL PRIMARY KEY IdENTITY,
   [OrderId] INT NOT NULL,
   [ItemId] INT NOT NULL,
   CONSTRAINT [FK_Orders2_OrderItems2] FOREIGN KEY ([OrderId]) REFERENCES [Orders2]([Id])
);

--== Populate some dummy data ==--

INSERT Customers(Name)
VALUES('Aaberg'),('Aalst'),('Aara'),('Aaren'),('Aarika'),('Aaron'),('Aaronson'),('Ab'),('Aba'),('Abad');

INSERT Orders(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()
FROM Customers;

INSERT OrderItems(OrderId, ItemId)
SELECT Id, Id*1000
FROM Orders;

INSERT Customers2(Name)
VALUES('Zysk'),('Zwiebel'),('Zwick'),('Zweig'),('Zwart'),('Zuzana'),('Zusman'),('Zurn'),('Zurkow'),('ZurheIde');

INSERT Orders2(CustomerId, OrderDate)
SELECT Id, Id+GETDATE()+20
FROM Customers2;

INSERT OrderItems2(OrderId, ItemId)
SELECT Id, Id*1000+10000
FROM Orders2;

SELECT * FROM Customers JOIN Orders ON Orders.CustomerId = Customers.Id JOIN OrderItems ON OrderItems.OrderId = Orders.Id;

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== ** START ACTUAL ANSWER ** ==--

--== Create Linkage tables ==--

CREATE TABLE CustomerLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);
CREATE TABLE OrderLinkage(old INT NOT NULL PRIMARY KEY, new INT NOT NULL);

--== Copy Header (Customers) rows and record the new key ==--

MERGE Customers2
USING Customers
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (Name) VALUES(Customers.Name)
OUTPUT Customers.Id, INSERTED.Id INTO CustomerLinkage;

--== Copy Detail (Orders) rows using the new key from CustomerLinkage and record the new Order key ==--

MERGE Orders2
USING (SELECT Orders.Id, CustomerLinkage.new, Orders.OrderDate
FROM Orders 
JOIN CustomerLinkage
ON CustomerLinkage.old = Orders.CustomerId) AS Orders
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (CustomerId, OrderDate) VALUES(Orders.new, Orders.OrderDate)
OUTPUT Orders.Id, INSERTED.Id INTO OrderLinkage;

--== Copy Detail (OrderItems) rows using the new key from OrderLinkage ==--

MERGE OrderItems2
USING (SELECT OrderItems.Id, OrderLinkage.new, OrderItems.ItemId
FROM OrderItems 
JOIN OrderLinkage
ON OrderLinkage.old = OrderItems.OrderId) AS OrderItems
ON 1=0 -- we just want an insert, so this forces every row as unmatched
WHEN NOT MATCHED THEN
INSERT (OrderId, ItemId) VALUES(OrderItems.new, OrderItems.ItemId);

--== ** END ACTUAL ANSWER ** ==--

--== Display the results ==--

SELECT * FROM Customers2 JOIN Orders2 ON Orders2.CustomerId = Customers2.Id JOIN OrderItems2 ON OrderItems2.OrderId = Orders2.Id;

--== Drop test tables ==--

DROP TABLE OrderItems;
DROP TABLE OrderItems2;
DROP TABLE Orders;
DROP TABLE Orders2;
DROP TABLE Customers;
DROP TABLE Customers2;
DROP TABLE CustomerLinkage;
DROP TABLE OrderLinkage;

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