Duplicate Data over One-to-Many self relation (Tsql)

Answer 1

you'll have to provide create table and insert into statements for little sample data. and expected results based on this sample data.

Answer 2

If I understand the problem, this might be what you want:

INSERT dbo.Master VALUES (@NewMaster_ID, @NewDescription)

INSERT dbo.Detail (parent_id, master_id, [name])
SELECT detail_ID, @NewMaster_ID, [name]
FROM dbo.Detail 
WHERE master_id = @OldMaster_ID

UPDATE NewChild
SET parent_id = NewParent.detail_id
FROM dbo.Detail NewChild
JOIN dbo.Detail OldChild
ON NewChild.parent_id = OldChild.detail_id
JOIN dbo.Detail NewParent
ON NewParent.parent_id = OldChild.parent_ID
WHERE NewChild.master_id = @NewMaster_ID
AND   NewParent.master_id = @NewMaster_ID
AND   OldChild.master_id = @OldMaster_ID

The trick is to use the old detail_id as the new parent_id in the initial insert. Then join back to the old set of rows using this relationship, and update the new parent_id values.

I assumed that detail_id is an IDENTITY value. If you assign them yourself, you'll need to provide details, but there's a similar solution.