Sql-Server
如何將遷移數據複製到具有標識列的新表,同時保留 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;