SQL: Copy records from child table to parent table, setting foreign key at the same time.

Question

I have 1-to-many relationship between two tables (Product and PLProduct respectively). This is done through foreign key PLProduct.ParentProductId which relates to Product.Id.

The question is: how can I copy (orphan) records from PLProduct to Product table, setting foreign key at the same time?

Answer 1

These two simple SQL statements should do it. This assumes that your CHILD table has a proper PK, since we'll leverage that uniqueness to create a parent record.

--INSERT a parent record with a name containing the child ID
INSERT INTO Parent1
SELECT 'Child_' + CAST(ID as varchar) FROM Child1 WHERE ParentID IS NULL

--UPDATE the child table from a join to the parent table on the name field
UPDATE Child1
SET Child1.ParentID = Parent1.ID
FROM
Child1
JOIN Parent1 ON 'Child_' + CAST(Child1.ID as varchar) = Parent1.ParentName

I'm using SQL2000, devoid of any interesting tricks (would like to see what else turns up, though.)