0

I have a database with a Parent(ParentID, ParentData) and Child (ChildID, ParentID, ChildData) table. I need to write a script that will copy both tables into a new DB but with new ParentID for both tables.

I know I could write something like:

INSERT INTO Parent(ParentID, ParentData)
SELECT newid(), ParentData FROM OtherDB.dbo.Parent

But how do I keep the children bound with the parent if I do that? Is there another, better way to do this that would allow me to keep them together?

Jeremy
  • 11
  • 2
  • 2
    Why do you need new ids, if the tables are in a different database? – Gordon Linoff Mar 11 '19 at 17:25
  • Because the OtherDB is something of an example DB and each DB being copied to is a multi-tenant DB which may need to copy these values into it multiple times. – Jeremy Mar 11 '19 at 18:08
  • 1
    You could use the OUTPUT clause to another table with columns for the new and original ID. That way you have a way to tie the new and old ids together so you can use that as a join when inserting the children. – Sean Lange Mar 11 '19 at 18:29

1 Answers1

0

What I ended up doing was creating a temp table with both new and old IDs that I could reference like so:

CREATE TABLE #TempParent(OldParentID, NewParentID, ParentData)

INSERT INTO #TempParent
SELECT(ParentID, newid(), ParentData)
FROM OtherDB.dbo.Parent

INSERT INTO Parent(ParentID, ParentData)
SELECT(NewParentID, ParentData)
FOM #TempParent

INSERT INTO Child(ParentID, ChildData)
SELECT p.NewParentID, c.ChildData
FROM OtherDB.dbo.Child c
LEFT JOIN #TempParent p ON p.OldParentID = c.ParentID

DROP TABLE #TempParent

And that works. I'm sure there's a much more elegant way to do this, though.

Jeremy
  • 11
  • 2