How to copy records from inter-linked tables to another in a different database?

Question

I have 3 tables that are inter-linked between each other. The design of the tables are as below.

• First (PK:FirstID, vchar:Name, int:Year)
• Second (PK:SecondID, FK:FirstID, int:Day, int:Month)
• Third (PK:ThirdID, FK:SecondID, int:Speed, vchar:Remark)

I'm trying to copy records from 3 inter-linked tables from Database A to Database B. So my Transact-SQL looks something like this:

INSERT INTO First
(Name, Year)
SELECT  Name, Year
FROM    DB_A.dbo.First
WHERE Year >= 1992

INSERT INTO Second
(FirstID, Day, Month)
SELECT  FirstID, Day, Month
FROM    DB_A.dbo.Second S INNER JOIN
        DB_A.dbo.First F ON S.FirstID = F.FirstID
WHERE Month > 6

INSERT INTO Third
(SecondID, Speed, Remark)
SELECT  SecondID, Speed, Remark
FROM    DB_A.dbo.Third T INNER JOIN
        DB_A.dbo.Second S ON T.SecondID = S.SecondID INNER JOIN
        DB_A.dbo.First F ON F.FirstID = S.FirstID
WHERE Remark <> NULL

These statements works all well and fine until the starting position of First.FirstID in Database A and B becomes not the same due to the three tables in Database B being empty. Hence, the constraint on foreign_key error is produced.

Possible Solutions

1. Reuse old First.FirstID One of the solution I have figured out is to use reuse the old First.FirstID from Database A. This can be done by setting SET IDENTITY_INSERT TableName ON just before the insert into TableName and including the TableName.TableNameID into the insert statement. However, I'm advised against doing this by my colleagues.

2. Overwrite Second.FirstID with new First.FirstID and subsequently, Third.SecondID with the new Second.SecondID I'm trying to apply this solution using OUTPUT and TABLE variable by outputting all First.FirstID into a temporary table variable and associate them with table Second similar to this answer However, I'm stuck on how to associate and replace the Second.FirstIDs with the correct IDs in the temporary table. An answer on how to do this would also be accepted as the answer for this question.

3. Using solution No. 1 and Update the primary and foreign keys using UPDATE CASCADE. I just got this idea but I have a feeling it will be very tedious. More research needs to be done but if there's an answer that shows how to implement this successfully, then I'll accept that answer.

So how do I copy records from 3 inter-linked tables to another 3 similar tables but different primary keys? Are there any better solutions than the ones proposed above?

Answer 1

You can use OUTPUT Clause.

CREATE TABLE #First (NewId INT PRIMARY KEY, OldId INT)  

INSERT INTO First
(
    Name, 
    Year,
    OldId -- Added new column 
)
OUTPUT Inserted.FirstID, Inserted.OldId INTO #First
SELECT  
    Name, 
    Year,
    FirstID -- Old Id to OldId Column
FROM    
    DB_A.dbo.First
WHERE 
    Year >= 1992

Second Table

CREATE TABLE #Second (NewId INT PRIMARY KEY, OldId INT) 
INSERT INTO Second
(
    FirstID, 
    Day, 
    Month,
    OldId -- Added new column 
)
OUTPUT Inserted.SecondID, Inserted.OldId INTO #Second
SELECT  
    OF.NewId, --FirstID
    Day, 
    Month,
    SecondID
FROM    
    DB_A.dbo.Second S INNER JOIN
    DB_A.dbo.First F ON S.FirstID = F.FirstID INNER JOIN
    #First OF ON F.FirstId = OF.OldId -- Old ids here
WHERE 
    Month > 6

Last one

INSERT INTO Third
(
    SecondID,
    Speed,
    Remark
)
SELECT  
    OS.NewId, -- SecondID
    Speed, 
    Remark
FROM
    DB_A.dbo.Third T INNER JOIN
    DB_A.dbo.Second S ON T.SecondID = S.SecondID INNER JOIN
    DB_A.dbo.First F ON F.FirstID = S.FirstID INNER JOIN
    #Second  OS ON S.SecondID = OS.OldId 
WHERE Remark <> NULL

Answer 2

First Solution

Using MERGE and OUTPUT together

OUTPUT combined with MERGE function has the ability to retrieve the old primary keys before inserting into the table.

Second Solution

NOTE: This solution only works if you are sure that you have another column that has its values unique in the table besides the table's primary key.

You may use this column as a link between the table in the source database and its sister table in the target database. The code below is an example taking into account that First.Name has unique values when month > 6.

-- no changes to insert code in First table
INSERT INTO First
(Name, Year)
SELECT  Name, Year
FROM    DB_A.dbo.First
WHERE Year >= 1992

INSERT INTO Second
(FirstID, Day, Month)
SELECT  CurrentF.FirstID, Day, Month -- 2. Use the FirstID that has been input in First table
FROM    DB_A.dbo.Second S INNER JOIN
        DB_A.dbo.First F ON S.FirstID = F.FirstID INNER JOIN
        First CurrentF ON CurrentF.Name = F.Name -- 1. Join Name as a link
WHERE Month > 6

INSERT INTO Third
(SecondID, Speed, Remark)
SELECT  CurrentS.SecondID, Speed, Remark --5. Get the proper SecondID
FROM    DB_A.dbo.Third T INNER JOIN
        DB_A.dbo.Second S ON T.SecondID = S.SecondID INNER JOIN
        DB_A.dbo.First F ON F.FirstID = S.FirstID INNER JOIN
        First CurrentF ON CurrentF.Name = F.Name INNER JOIN -- 3. Join using Name as Link
        Second CurrentS ON CurrentS.FirstID= CurrentF.FirstID -- 4. Link Second and First table to get the proper SecondID.
WHERE Remark <> NULL