Copying a 1-to-many relationship between two tables to another two tables (SQL 2005)

Question

Hi everyone,

I have two tables with a parent-child relationship. I would like to copy some of their records to two other tables, also with a parent-child relationship, but with a slightly different table structure.

There is a foreign key involved with both sets of tables, an integer column. All tables have the identity increment on for their primary key columns.

If I do a SELECT INTO from the source parent table to the destination parent table, the primary key values for the destination records will be different from the source records, and the parent-child relationship will be lost.

Does anyone know how I can preserve this relationship during the copy, given that I'll have new primary key values in the new parent table? I'd prefer not to set the identity increment off for the new tables during this copy procedure, because there's no guarantee the primary key values in the source table won't already be in the destination.

Hope my description makes sense, and thanks for your opinions. Let me know if I can clarify further.

Answer 1

You'd have to maintain the original ID in a separate column in the table with the corresponding parentID. So in your destination table, you'd need to add an parentID_orig column (not auto-number) to retain the link.

Answer 2

When I have done this, I did so by preserving the old ID after the insert into the new table.

Some people add a temporary column with the old ID, but I know of a better way.

You can use the OUTPUT clause to insert the inserted records into a temp table (with the new and the old IDs as a mapping table. Then join to that when inserting the child records.

Something like this (crude)

DECLARE @MyTableVar TABLE (
    NewID INT,
    OldID INT
)

INSERT NewParentTable
OUTPUT
    INSERTED.ID,
    old.ID
INTO @MyTableVar
SELECT *
FROM OldParentTable old


INSERT NewChildTable
SELECT
    old.ID,
    t.NewID,
    old.name
FROM OldChildTable old
INNER JOIN @MyTableVar t ON t.OldID = old.ParentID