CURSOR is driving me insane!

We have a tree structure containing section names. Each node is asection name and each section can have subsections. I have to copy thetree structure but need to maintain the parent-child relationshipestablished 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 isthe identity of the section record and parent_id contains NULL or theID 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 wouldlike to copy this sucture to create 4 new sections but they need tomaintain their id/parent_id relationships BUT with new IDs. For this icreated 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 FORUPDATE 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
------

Thecritical "UPDATE @.tblSection" part doesnt seem to update the tempvariable table with the @.newSectionID (the actual section identityobtained after inserting a real record into the tblSection table). Soin the end the inserted records into tblSection still point to theincorrect 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? I've tried using both table variables and temp tables but no luck.

I've only quickly scanned your code, but it makes no sense that the UPDATE statement does not work. It should. And since it's not, that means your WHERE condition is not being met.

So, backtracking and looking at your INSERT statement, you have 3 columns listed yet are inserting only 2 values. I am guessing that this is the cause of your problem. You are assigning the @.tcaption value to the parent_id column:

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

|||Great stuff. Thanks Terri. All is good.