How to Migrate Data to New Tables with Identity Column While Preserving FK Relationship in SQL Server

foreign keyidentitysql server

I want to migrate data from one database to another. The table schemas are exactly the same:

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])
)

The two databases have different data, so the new identity key for the same table would be different at the two databases. That is not a problem; my goal is to append new data to the existing one, not complete replacing all data of the entire table. However I would like to maintain all parent-child relationship of the inserted data.

If I use the "Generate Script" feature of SSMS, the script would attempt to insert using the same ID, which would conflict existing data in the destination database. How can I copy data using database scripts only?

I want the identity column at the destination to continue normally from its last value.

Customers does not have any other UNIQUE NOT NULL constraint. It is ok to have duplicate data in other columns (I'm using Customers and Orders just as an example here, so I don't have to explain the whole story). The question is about any one-to-N relationship.

Best Answer

Here's a way that scales easily to three related tables.

Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for related tables mapping.

The actual answer is just two create table statements and three merges. The rest is sample data setup and tear down.

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;
Related Question