Mark Mark - 4 months ago 9
SQL Question

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

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

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
Comments