Home » SQL & PL/SQL » SQL & PL/SQL » HELP - Cursor, Table Variable, and Update
HELP - Cursor, Table Variable, and Update [message #214026] Sat, 13 January 2007 13:55 Go to next message
quantass
Messages: 20
Registered: April 2006
Junior Member
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?

BTW we're using MS Sql Server but i think the problem lies with SQL in general. Thanks.

[Updated on: Sat, 13 January 2007 13:56]

Report message to a moderator

Re: HELP - Cursor, Table Variable, and Update [message #214028 is a reply to message #214026] Sat, 13 January 2007 15:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>BTW we're using MS Sql Server but i think the problem lies with SQL in general. Thanks.
So why in the world are you posting your question in an ORACLE forum????????????????????
Your syntax would error out on a major way in Oracle.
Re: HELP - Cursor, Table Variable, and Update [message #214030 is a reply to message #214028] Sat, 13 January 2007 15:57 Go to previous messageGo to next message
quantass
Messages: 20
Registered: April 2006
Junior Member
As i said in my post I believe the problem is a SQL problem more than a propreitary issue. Thats why i posted under SQL Newbies.
Re: HELP - Cursor, Table Variable, and Update [message #214033 is a reply to message #214026] Sat, 13 January 2007 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Oracle SQL <> MS SQL
Plus IMO, what was posted was NOT SQL but MS equivalent of PL/SQL
SQL does NOT contain DECLARE, BEGIN, etc.
Re: HELP - Cursor, Table Variable, and Update [message #214036 is a reply to message #214026] Sat, 13 January 2007 18:15 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Quote:
create table goo (id number(10,3) not null , caption varchar2(200),
pid number(10,3))
/






Quote:
begin
insert into goo (id , caption , pid ) values (2,'sect2',null);
insert into goo (id , caption , pid ) values (1,'sect1',null);
insert into goo (id , caption , pid ) values (3,'sect1_child',1);
insert into goo (id , caption , pid ) values (4,'sect2-child',2);
end;
/




curamgph>select id,pid,caption from goo order by id,pid;

ID PID CAPTION
---------- ---------- ---------------
1 sect1
2 sect2
3 1 sect1_child
4 2 sect2-child




=====================

Quote:
insert into goo (id,pid,caption )
select id+rn,pid+prn,caption from
(
with incr as (
select id,id+mx rn from goo,
( select max(id) mx from goo ) foo
)
select goo.id,incr.rn,goo.pid,goo.caption, i2.rn prn
from incr ,goo,incr i2
where incr.id=goo.id and
i2.id(+)=goo.pid
)


=========

4 row will be inserted.


curamgph>select id,pid,caption from goo order by id,pid;

ID PID CAPTION
---------- ---------- ---------------
1 sect1
2 sect2
3 1 sect1_child
4 2 sect2-child
6 sect1
8 sect2
10 6 sect1_child
12 8 sect2-child

8 rows selected.



================


I have no clue how you would do this in SQL server.

Srini






Previous Topic: pl/sql block
Next Topic: Cannot create table??
Goto Forum:
  


Current Time: Thu Dec 08 08:16:07 CST 2016

Total time taken to generate the page: 0.30258 seconds