I have a table like this :
CREATE TABLE IF NOT EXISTS THING(
Id int NOT NULL IDENTITY(1, 1),
IdParent int,
randomtext varchar(255)
)
I would like to copy given list of ids with their children, grandchildren, but when I try it keeps ancient parent Id so the hierarchy is not kept given something like this :
| Id | IdParent | random text |
-------------------------------
| 1 | 0 | "aaaaaaaaa" |
| 2 | 1 | "tt" |
| 3 | 2 | "third" |
| 4 | 0 | "fourth" |
| 5 | 0 | "randOther" |
If I give the ids list (1, 4), and another parntid value like "10", it copies rows with id 1 and 4 with the new parent id as 10 but the duplicated children get new inserted parents so it keeps the hierarchy
| Id | IdParent | random text |
-------------------------------------
| 1 | 0 | "aaaaaaaaa" |
| 2 | 1 | "tt" |
| 3 | 2 | "third" |
| 4 | 0 | "fourth" |
| 5 | 0 | "randOther" |
| 6 | 10 | "aaaaaaaaa -copy" |
| 7 | 6 | "tt- copy" |
| 8 | 7 | "third- copy" |
| 9 | 10 | "fourth- copy" |
All i managed to do for now is place all copies into the new parent with this query but I don't want to place all children in the new parent
WITH HIERARCHY (Id)
AS (SELECT Id FROM THING
WHERE Id IN (1,4)
UNION ALL
SELECT e.Id
FROM THING e
INNER JOIN HIERARCHY h ON e.IdParent = h.Id
)
INSERT INTO THING (IdParent, randomtext)
SELECT 10, randomtext
FROM THING
WHERE Id IN (SELECT * FROM HIERARCHY)
(the query(ies) should work in sql server 2008 and oracle)
