Help with CURSOR

We have a tree structure containing section names. Each node is a section name and each section can have subsections. I have to copy the tree structure but need to maintain the parent-child relationship established within the id / parent_id fields. How do i acheive this?

For example i have the tree
Section 1
|-Section 1.1
Section 2
|-Section 2.1

The "Section" table contains 3 fields: id, parent_id, and caption. ID is the identity of the section record and parent_id contains NULL or the ID of this record's parent to create a child. So "Section 1" (id=1, parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1" (id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).

I would like to copy this sucture to create 4 new sections but they need to maintain their id/parent_id relationships BUT with new IDs. For this i created the following stored procedure:
-
CREATE PROCEDURE [dbo].[CopySection]
AS
-- Declare a temporary variable table for storing the sections
DECLARE @.tblSection TABLE
(
id int,
parent_id int,
caption varchar(max),
)

DECLARE @.newAgendaID int, @.newSectionID int;
DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max);

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table
INSERT INTO @.tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID
DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FOR UPDATE OF parent_id;
OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Insert the new Section and record the identity
INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@.tparent_id, @.tcaption);
SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection
-- Update all temp variable records to point to the new parent_id
UPDATE @.tblSection SET parent_id = @.newSectionID WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
END

CLOSE c1
DEALLOCATE c1

END
-

The critical "UPDATE @.tblSection" part doesnt seem to update the temp variable table with the @.newSectionID (the actual section identity obtained after inserting a real record into the tblSection table). So in the end the inserted records into tblSection still point to the incorrect parent_id instead of the copied record's parent_id.

Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset?

You can use the following query.. Here we are not using any cursor..

Declare @.Section Table
(
ParentId int,
Caption varchar(100)
)
Declare @.ParentId as int;
Declare @.NewParentId as int;
Declare @.Caption as varchar(100);

Insert into @.Section
Select Id,Caption from tblSection Where Parent_ID is NULL -- you can filter your desired id
While Exists(Select 1 From @.Section)
Begin
Select Top 1 @.ParentId = ParentId, @.Caption = Caption From @.Section;
Insert Into tblSection(parent_id, caption) values(null, @.Caption);

Select @.NewParentId =Scope_Identity();
Delete From @.Section Where ParentId = @.ParentId ;

Insert Into tblSection(parent_id, caption)
Select @.NewParentId, caption From tblSection Where parent_id = @.ParentId;
End

Select * From tblSection;

|||

I have tried quantass 's code and it does not do the parentid properly as it keeps using the old parentid of the previous tree structure - can you please show me the actual code you got it working or how can it be solved please?

I really am in desperate need of getting one that actually works properly.

Thanks

|||Which version of SQL server are you running?|||

I am using SQL server 2005 - I think I have tried to get it working but I don't know if this is the correct approach which I have modified quanass's sql.

-- Declare a temporary variable table for storing the sections

DECLARE @.tblSection TABLE

(

id int,

parent_id int,

caption varchar(max),

old_id int

)

DECLARE @.newSectionID int;

DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max),@.told_id int;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table

INSERT INTO @.tblSection SELECT id, parent_id, caption, old_id FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID

DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FOR UPDATE OF parent_id;

OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption,@.told_id;

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Insert the new Section and record the identity

INSERT INTO tblSection (parent_id, caption) VALUES (@.tparent_id, @.tcaption);

SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection

-- Update all temp variable records to point to the new parent_id

UPDATE @.tblSection SET parent_id = @.newSectionID, old_id = @.tid WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption,@.told_id;

END

CLOSE c1

DEALLOCATE c1

SELECT * FROM @.tblSection <from this point I tried to get the parentid done properly and not hanging onto the old parentID

WHILE EXISTS(SELECT 1 FROM @.tblSection)

BEGIN

SELECT @.tparent_id=parent_id,@.told_id=old_id FROM @.tblSection

UPDATE tblSection SET[parent_id] = @.tparent_id WHERE parent_id = @.told_id

DELETE FROM @.tblSection WHERE UpLevelID = @.tUpLevelID

END

END

I could do with some feedback and whether it can be improved and what can be changed on this?

Thanks

|||

I have tried with the modified SQL as above and it did work with small data. But I found if I tried with 100 records - the parentid gets really messed up and doesn't look right.

I would really appreciate any help and examples or show me the corrections on my modified SQL please as I have spent a week struggling in vain to actually get it working properly.

ID ParentID MenuID Name
====================
1 0 1 sun
2 1 1 mars
3 2 1 charon
4 2 1 flubbie
5 3 1 blub

6 0 2 sun <-new menu as menuID of 1as it is a copy of it
7 6 2 mars
8 7 2 charon
9 7 2 flubbie
10 8 2 blub

Please help as I am in desperate situation on this. Thanks

|||I will give it a look and see what I can come up with; sorry I didn't respond yesterday. I've been off in DB2 land most of this week.|||

This is my first pass and I feel like I didn't do a good job with it. It sure looks WAY more long-winded than your version. Also, I don't care for the WHILE loop in my stored procedure, but I have to somehow retrieve the IDENTITY values; nonetheless, it at least seems to to work. First, I defined this table for the mock-up:

create table dbo.jHierMenu
( ID integer identity (21, 1)
constraint pk_jHierMenu primary key,
ParentID integer null,
MenuID integer null,
[Name] varchar(20) not null
)
go

create index jHierMenu_Menu_ndx
on dbo.jHierMenu (MenuID, ParentID, ID, [Name])
go

insert into dbo.jHierMenu values (0, 1, 'sun')
insert into dbo.jHierMenu values (21, 1, 'mars')
insert into dbo.jHierMenu values (22, 1, 'charon')
insert into dbo.jHierMenu values (22, 1, 'flubbie')
insert into dbo.jHierMenu values (23, 1, 'blub')
select * from dbo.jHierMenu

I then created a function that I could use as a building block:

create function dbo.relativeMenuHierarchy
( @.pm_MenuID integer
)
returns @.menuHierarchy table
( rid integer identity primary key,
ParentRid integer null,
ID integer not null,
ParentID integer null,
MenuID integer not null,
[Name] varchar (20) null,
hierLevel integer not null,
unique (hierLevel, rid)
)
as
begin

if ( select count(*) from dbo.jHierMenu
where menuId = @.pm_menuID
) < 1
return;

;with menuHierCTE
as
(
select id,
ParentID,
MenuID,
[Name],
0 as hierLevel
from dbo.jHierMenu
where MenuID = @.pm_menuID
and ParentID = 0
union all
select a.ID,
a.ParentID,
a.MenuID,
a.[Name],
cte.hierLevel + 1
from menuHierCTE as cte
inner join dbo.jHierMenu a
on a.menuId = @.pm_menuID
and a.parentID = cte.id
)
insert into @.menuHierarchy
select case when parentId = 0 then 0 else null end,
id,
parentId,
menuId,
[Name],
hierLevel
from menuHierCTE

declare @.secondHierarchy table
( rid integer primary key,
ParentRid integer null,
ID integer not null,
ParentID integer null,
MenuID integer not null,
[Name] varchar (20) null,
hierLevel integer not null,
unique (hierLevel, rid)
)

insert into @.secondHierarchy
select * from @.menuHierarchy

update @.menuHierarchy
set ParentRid = a.rid
from @.secondHierarchy a
inner join @.menuHierarchy b
on a.hierLevel = b.hierLevel - 1
and a.id = b.parentId
inner join small_iterator (nolock) i
on iter = b.hierLevel
and iter <= ( select max(hierLevel)
from @.secondHierarchy
)

return

end

go

select rid,
parentRid,
id,
parentId,
menuId,
[name],
hierLevel
from relativeMenuHierarchy (1);

-- rid parentRid id parentId menuId name hierLevel
-- -- -- --
-- 1 0 21 0 1 sun 0
-- 2 1 22 21 1 mars 1
-- 3 2 23 22 1 charon 2
-- 4 2 24 22 1 flubbie 2
-- 5 3 25 23 1 blub 3

Once I had the function working, I assembled a stored procedure to perform the actual work:

create procedure dbo.replicateMenu
( @.pm_oldMenuId integer,
@.pm_newMenuId integer
)
as

if exists
( select 0 from jHierMenu
where menuId = @.pm_newMenuId
)
begin
raiserror ('Menu ID ''%d'' is NOT a new menu id!', 0, 1, @.pm_newMenuID)
return 21001
end

declare @.menuHierarchy table
( rid integer primary key,
ParentRid integer null,
ID integer null,
ParentID integer null,
MenuID integer not null,
[Name] varchar (20) null,
hierLevel integer not null,
unique (hierLevel, rid)
)

insert into @.menuHierarchy
select rid,
parentRid,
null as ID,
null as ParentId,
menuId,
[name],
hierLevel
from dbo.relativeMenuHierarchy (@.pm_oldMenuID)

declare @.nextRid integer
declare @.lastId integer
declare @.maxRid integer

set @.maxRid = ( select max(rid) from @.menuHierarchy )

if @.maxRid = 0
return 0

insert into dbo.jHierMenu
select 0 as parentId,
@.pm_newMenuId as [MenuId],
[Name]
from @.menuHierarchy
where rid = 1
set @.lastId = scope_identity()

update @.menuHierarchy
set id = @.lastId,
parentId = 0
where rid = 1

set @.nextRid = 1
while @.nextRid < @.maxRid
begin

set @.nextRid = @.nextRid + 1

insert into dbo.jHierMenu
select p.id as ParentId,
@.pm_newMenuId as [MenuId],
a.[name]
from @.menuHierarchy a
inner join @.menuHierarchy p
on a.parentRid = p.rid
where a.rid = @.nextRid

set @.lastId = scope_identity()

update @.menuHierarchy
set id = @.lastId,
parentId = h.parentId
from @.menuHierarchy a
inner join dbo.jHierMenu h
on h.id = @.lastId
and a.rid = @.nextRid

end

return 0

go

exec replicateMenu 1, 101

select * from jHierMenu

-- ID ParentID MenuID Name
-- -- -- --
-- 21 0 1 sun
-- 22 21 1 mars
-- 23 22 1 charon
-- 24 22 1 flubbie
-- 25 23 1 blub

-- 65 0 101 sun
-- 66 65 101 mars
-- 67 66 101 charon
-- 68 66 101 flubbie
-- 69 67 101 blub

delete from jHierMenu where menuId = 101

I will try this out with a 100 and 200 row test. I'm kinda fried right now and I must step back for at least a little while.

|||

I tried the stored procedure with a 100-row menu and I feel better about all of this -- especially the function. I was able to use the function compare the two menus and verify that the replication had worked correctly. This also means that it would be relatively easy to create a "compareMenu" stored procedure or function. I started by truncating the mock-up table and inserting 100 random rows:

truncate table dbo.jHierMenu
go

set identity_insert tempdb.dbo.jHierMenu ON

insert into dbo.jHierMenu
( id,
parentId,
menuId,
name
)
select 1 as id,
0 as parentId,
1 as menuId,
'Name 1' as [Name]

insert into dbo.jHierMenu
( id,
parentId,
menuId,
name
)
select iter as id,
floor(1 + (cast (iter as float) - 1.99999) * dbo.randValue(iter))
as parentId,
1 as menuId,
'Name ' + convert (varchar (5), iter) as [Name]
from small_iterator
where iter > 1
and iter <= 100

go

set identity_insert tempdb.dbo.jHierMenu Off

go

select * from dbo.jHierMenu order by id

-- ID ParentID MenuID Name
-- -- --
-- 1 0 1 Name 1
-- 2 1 1 Name 2
-- 3 1 1 Name 3
-- 4 1 1 Name 4
-- 5 3 1 Name 5
-- 6 4 1 Name 6
-- 7 5 1 Name 7
-- ...
-- 99 84 1 Name 99
-- 100 12 1 Name 100

I then ran the "replicateMenu" stored procedure and then ran the query that follows to verify that the new menu was identical in structure to the old menu:

set nocount on
--delete from dbo.jHierMenu where menuId = 101
exec replicateMenu 1, 101

-- -
-- Uncomment the AND statement if you want to display
-- only the exceptions.
-- -
select a.rid,
a.[name],
b.rid,
b.[name]
from relativeMenuHierarchy (1) a
inner join relativeMenuHierarchy (101) b
on a.rid = b.rid
-- and a.name <> b.name

-- rid name rid name
-- - -
-- 1 Name 1 1 Name 1
-- 2 Name 2 2 Name 2
-- 3 Name 3 3 Name 3
-- ...
-- 99 Name 84 99 Name 84
-- 100 Name 99 100 Name 99

|||

Hi

I had read through your examples and it seems much cleaner and efficient than mine (a compliment). I have tested out my stored procedure several times and it does work (at least I think it does). However I am going try yours as well and test it out again.

I also realise supposing someone comes a long and deletes an item and the rest of items becomes obselete if you like as the first item is missing so the uplevel gets skewed up. So if they copy that menu with the missing item and the rest of the items with obselete uplevel, theoretically, the copied menu would have the rest of the items with the old obselete uplevelID - would that break the stored procedure coding or mess the structure when copying a menu? I noticed it does happen with my old stored proc - could that easily be remedied?

I really appreciate the time and effort you have put in this stored procedure and my gratitude of thanks goes towards you. I found what you have done with the stored procedure and testing has been a big lesson for me and hopefully I will improve my 'thinking out of the box'.

Thanks again, will keep you posted to let you know how I get on with the testing on this.