Is there a way to duplicate part of a adjacent list hierarchy given a starting id?

Question

I have an adjacent list hierarchy model that makes up a topic structure

   ID   Parent_Id Topic_Name
   1    Null      Topic 1
   2    Null      Topic 2
   3    2            Topic 3
   4    3               Topic 4
   5    2            Topic 5
   6    Null      Topic 6

This forms part of an application which I cant change - the topics dont have multiple parents so unfortunatly I can't move to a nested sets - although if this was an interim step in the process - this would be fine as long as it went back to adjacent list hierarchy model

I want to specify a topic id and then copy it to a new topic id and retain the levels / structure underneath

So in my example I could specify topic topic_id 2 and it would create

   ID   Parent_Id Topic_Name
   7    Null      Topic 2
   8    7            Topic 3
   9    8               Topic 4
   10   7            Topic 5

Auto numbering is taken care of for the ID so no need to construct that, but obviously the parent id needs to be retained

How can I achieve the above? would I need to flatten the data and do 3 seperate inserts logging the id after each insert?

What version of SQL Server do you use? Is `ID` an [identity column](http://msdn.microsoft.com/en-us/library/ms186775.aspx)? — Mikael Eriksson, Oct 03 '12 at 11:31

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

2

You can use a recursive CTE to get the rows to insert. If you use merge to add the rows you can use output to capture a mapping between the generated ID and the old ID which can be used to update the column Parent_ID for the inserted rows.

-- ID for topic to copy 
declare @ID int;
set @ID = 2;

-- Table to hold the inserted rows
declare @T table
(
  New_ID int,
  Old_ID int,
  Old_ParentID int
);

-- Add rows from recursive CTE using merge
with C as
(
  select T.ID, T.Parent_Id, T.Topic_Name
  from YourTable as T
  where T.ID = @ID
  union all
  select T.ID, T.Parent_Id, T.Topic_Name
  from YourTable as T
    inner join C 
      on C.ID = T.Parent_Id
)
merge YourTable
using C
on 0 = 1
when not matched then
  insert (Topic_Name) values (C.Topic_Name)
output inserted.ID,
       C.ID,
       C.Parent_Id
  into @T(New_ID, Old_ID, Old_ParentID);

-- Update Parent_Id for the new rows
update Y set
  Parent_Id = T2.New_ID
from @T as T1
  inner join @T as T2
    on T1.Old_ParentID = T2.Old_ID
  inner join YourTable as Y
    on T1.New_ID = Y.ID;

SE-Data

edited Jan 18 '21 at 12:30

Community

1
1

answered Oct 03 '12 at 11:53

Mikael Eriksson

136,425
22
210
281

Eeek! im getting a AK_Topc_nm constraint error, Mikael are you avalible for a chat? – Rob Oct 03 '12 at 14:03
@Rob - Not now, have to run. Would help if you could post the definition of the constraint? What is it constraining? – Mikael Eriksson Oct 03 '12 at 14:05
ALTER TABLE [dbo].[topics] ADD CONSTRAINT [AK_topc_nm] UNIQUE CLUSTERED ( [domain_id] ASC, [parent_topic_id] ASC, [topic_nm] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO – Rob Oct 03 '12 at 14:23
Im guessing its something to do with topic names being the same at different levels – Rob Oct 03 '12 at 14:24
1

@Rob - Yes. It is because all topics are inserted at root level and then the update builds the tree. If your `topic_name` field size is at least 36 characters you can temporary fill in a GUID in the topic name, output the generated GUID and the old topic name to the temporary table and update the topic name to the original value in the update statement. [Something like this](http://data.stackexchange.com/stackoverflow/query/81643). – Mikael Eriksson Oct 03 '12 at 15:15
ok Ill give it a go - if this works im post bounty'ing - this has saved me a lot of heart ache – Rob Oct 03 '12 at 15:37
I tried it just as I left work adapted it a bit to suit my specific needs and It works a treat! - seams my award bounty in 2 days has vanished - I will keep checking – Rob Oct 03 '12 at 20:00
@Rob Glad to hear I could be of help. – Mikael Eriksson Oct 03 '12 at 20:05

RichardTheKiwi · Answer 2 · 2012-10-03T12:05:21.500

Working example. Works in theory but probably not robust for high transaction volumes or fast enough for big tables.

--- sample table
create table tbl (
  id int identity primary key,
  parent_id int references tbl(id),
  topic_name varchar(100));
insert tbl values
(       Null,  'Topic 1'),
(       1,  '   Topic 2'),
(       2   ,  '       Topic 3'),
(       3   ,  '          Topic 4'),
(       2   ,  '       Topic 5'),
(       Null,  '    Topic 6'),
(       4,  '    Topic 4-3'),
(       7,  '    Topic 5-4')
;

--- script to duplicate a hierarchy branch
declare @inserttbl table (
  id int,
  parent_id int,
  topic_name varchar(100));
;with cte as (
  select id, parent_id, topic_name
  from tbl
  where id=2 -- or parameter
  union all
  select t.id, t.parent_id, t.topic_name
  from tbl t
  join cte c on t.parent_id=c.id
), cte2 as (
  select *,rn=row_number() over (order by id)
  from cte
), cte3 as (
  select rec.*, par.rn as parent_rn
  from cte2 rec
  left join cte2 par on par.id=rec.parent_id
)
insert @inserttbl
select cte3.rn,
       case when cte3.rn=1 then cte3.parent_id
            else cte3.parent_rn end,
       topic_name
from cte3;

insert tbl(topic_name)
select topic_name from @inserttbl order by id;

declare @delta int=scope_identity()-@@rowcount;

update t
set parent_id = i.parent_id + case when i.id=1 then 0
                              else @delta end
from tbl t
join @inserttbl i on t.id - @delta = i.id;

--- check results
select * from tbl;

Thats amazing - what if I want to go 4 levels deep? dont worry 4 is the max I can see at the moment - this is a one time job — Rob, Oct 03 '12 at 11:16

Is there a way to duplicate part of a adjacent list hierarchy given a starting id?

2 Answers2

Linked