Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
quantass
Starting Member
5 Posts |
 Posted - 2007-01-13 : 18:19:52
|
| 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 treeSection 1 |-Section 1.1Section 2 |-Section 2.1The "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? |
|
|
|
|
|
|
|
Help with CURSOR