SPROC help for insert into from related records

[EJO]

with sql 2000 enterprise

Trying to build a stored procedure that will take the rows of a parent
table, insert them into another table as well as the rows from a child
table to insert into another table and be able to maintain the
relationships between the parent/child rows of the new records.
Something like

old_id
new_id
t1 = select field1 from table where id=old_id
t2 = select field1, child_id, id from table

While not t1.eof
with t2
addnew
t2.field1=t1.field1
t2.child_id = new_id
update
t3 = select field1 from table1 where child_id=t1.id
t4 = select field1, child_id from table1
while not t3.eof
with t4
addnew
t4.field1 = t3.field1
t4.child_id = t2.id
update
end
t3.movenext
loop
end
t1.movenext
loop

I need to pass old_id and retrieve new_id and am unsure of the best
method to accomplish the whole thing.

[ng]

On 5 Sep., 18:09, EJO <[e-mail]:

with sql 2000 enterprise

Trying to build a stored procedure that will take the rows of a parent
table, insert them into another table as well as the rows from a child
table to insert into another table and be able to maintain the
relationships between the parent/child rows of the new records.
Something like

old_id
new_id
t1 = select field1 from table where id=old_id
t2 = select field1, child_id, id from table

While not t1.eof
*with t2
* addnew
* * t2.field1=t1.field1
* * t2.child_id = new_id
* update
* t3 = select field1 from table1 where child_id=t1.id
* t4 = select field1, child_id from table1
* *while not t3.eof
* * with t4
* * * addnew
* * * *t4.field1 = t3.field1
* * * *t4.child_id = t2.id
* * * update
* * end
* * t3.movenext
* * loop
* *end
* t1.movenext
* loop

I need to pass old_id and retrieve new_id and am unsure of the best
method to accomplish the whole thing.

Hi EJO,

I wouldn't youse a cursor. Here's my proposal:

======================
USE master
GO

--create some test tables, delete if scripts runs before
IF NOT OBJECT_ID('t1src') IS NULL BEGIN
DROP TABLE t1src
END
IF NOT OBJECT_ID('t2src') IS NULL BEGIN
DROP TABLE t2src
END
IF NOT OBJECT_ID('t3dst') IS NULL BEGIN
DROP TABLE t3dst
END
IF NOT OBJECT_ID('t4dst') IS NULL BEGIN
DROP TABLE t4dst
END

CREATE TABLE t1src (ID int)
CREATE TABLE t2src (ID int, ID_t1src int, Field1 int)

CREATE TABLE t3dst (ID_New int, ID_Old int)
CREATE TABLE t4dst (ID int, ID_t3dst int, Field1 int)

--insert some dummy data
INSERT INTO t1src (ID) VALUES (100)
INSERT INTO t1src (ID) VALUES (101)
INSERT INTO t1src (ID) VALUES (102)
INSERT INTO t1src (ID) VALUES (103)

INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1000, 100, 99)
INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1001, 100, 99)
INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1002, 100, 99)
INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1003, 101, 99)
INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1004, 101, 99)
INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1005, 102, 99)
INSERT INTO t2src (ID, ID_t1src, Field1) VALUES (1006, 103, 99)
IF NOT OBJECT_ID('usp_copyRows') IS NULL BEGIN
DROP PROCEDURE usp_copyRows
END
GO
--create the procedure
CREATE PROCEDURE usp_copyRows @old_ID int
AS
DECLARE @ID_New int
--select a new id (MAX + 1)
SELECT
@ID_New = (CASE WHEN MAX(ID_New) IS NULL THEN 0 ELSE MAX(ID_New)
END) + 1
FROM t3dst

--copy t1 row
INSERT INTO t3dst
(ID_New
, ID_Old)
SELECT
@ID_New
, ID
FROM t1src
WHERE ID = @old_ID


--copy child rows
INSERT INTO t4dst
(ID,
ID_t3dst,
Field1)
SELECT
ID
, @ID_New
, Field1
FROM t2src
WHERE ID_t1src = @old_ID

--return new id
RETURN @ID_New

GO


--call the proc
DECLARE @IDNew int
EXEC @IDNew = usp_copyRows @old_ID = 100
select @IDNew
GO

--if you do it twice, it works, too.
DECLARE @IDNew int
EXEC @IDNew = usp_copyRows @old_ID = 100
select @IDNew
GO

--SELECT * FROM t1src
--SELECT * FROM t2src

--select the copied data
SELECT * FROM t3dst
SELECT * FROM t4dst

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


Michael
http://www.sql-server.zankl-it.de