4

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.

Nate
  • 30,286
  • 23
  • 113
  • 184
larryq
  • 15,713
  • 38
  • 121
  • 190

2 Answers2

4

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
Gabriel McAdams
  • 56,921
  • 12
  • 61
  • 77
  • Thank you. Sounds very promising – larryq Oct 19 '10 at 16:53
  • 1
    Strange, I'm getting an error in the OUTPUT statement, regarding "the multi-part identifier old.ID could not be found." In all the reading I've done on OUTPUT, none of them have examples that didn't prefix the output columns with either INSERTED or DELETED. Can I use a table name as a prefix in OUTPUT? – larryq Oct 19 '10 at 21:23
  • 1
    I just looked into it, and it seems `the OUTPUT INTO clause is not supported in INSERT statements that contain a clause.` – Gabriel McAdams Oct 19 '10 at 21:37
  • I saw the same thing too. There's a thread that discusses this: http://stackoverflow.com/questions/155321/what-columns-can-be-used-in-output-into-clause And, here's an excellent discussion of the problem in MSDN: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/c1fe0b9c-9305-4cb0-9237-9374ff4fc03e/ – larryq Oct 19 '10 at 21:47
  • Jamie Thomson says that he can do it with a Merge statement. I wonder if he is talking about the same thing. I don't have a 2008 DB here to play with, or I would try some things. – Gabriel McAdams Oct 19 '10 at 22:22
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.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • Thanks for the tip, I was thinking about something along these lines at first. If Gabriel's idea doesn't pan out I'll look into it. – larryq Oct 19 '10 at 16:54