Hi all,
I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:
(id, parentId, label)
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
now i want to copy object 3 (UK) and it's children, so i would get
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
5, 0, UK_copy
6, 5, London_copy
I have this sproc:
Code Snippet
alter proc CopyObject
(@.ObjectId int,
@.NewParentId int)
as
declare @.NewId int,
@.NewName varchar
select @.NewId = max(Id) + 1 from Object
select @.NewName = [Name] + 'copy' from [Object] where Id = @.ObjectId
-- copy object
INSERT INTO [Object]
([Id]
,[Name]
,[ParentId]
select @.NewId,
@.NewName,
@.NewParentId
from [Object]
where Id = @.ObjectId
-- copy children and set their parent to the newly created object
declare c cursor fast_forward for
select Id
from [Object]
where ParentId = @.ObjectId
declare @.ChildId int
open c
fetch next from c into @.ChildId
while @.@.fetch_status = 0
begin
exec CopyObject
@.ObjectID = @.ChildId,
@.NewParentId = @.NewId
fetch next from c into @.ChildId
end
close c
deallocate c
But htis throws an error that the cursor already exists:
Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66
A cursor with the name 'c' already exists.
Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72
The cursor is already open.
I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.
Any ideas?
Thanks in advance,
Gert-Jan
The error makes sense as you are doing recursive calls to CopyObject, which would try to create a new cursor called "c" before closing and destroying the last one.
You could try CLR stored procedure (assuming you have SQL Server 2005), from where (e.g. in C#) you can also do recursion, but without the cursor name confict problem.
|||
I made the whole script dynamic instead, adding an extra parameter @.cycledepth, that i increment every time the proc calls itself, and then call the cursor 'c' + cast(@.cycledepth as varchar). That did the trick, although it's a bit slower tahn i had hoped, but that's probably because of a few triggers i should disable and then enable at the end.
