473,549 Members | 2,682 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.fi eld1
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.
Sep 5 '08 #1
1 2642
ng
On 5 Sep., 18:09, EJO <MyD...@gmail.c omwrote:
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.fi eld1
* * 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('t1sr c') IS NULL BEGIN
DROP TABLE t1src
END
IF NOT OBJECT_ID('t2sr c') IS NULL BEGIN
DROP TABLE t2src
END
IF NOT OBJECT_ID('t3ds t') IS NULL BEGIN
DROP TABLE t3dst
END
IF NOT OBJECT_ID('t4ds t') 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




Sep 5 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
8575
by: adrian | last post by:
hi all this is my first post to this group, so pls bear with me while i try to make some sense. i am trying to create a sproc which uses dynamic sql to target a particuar table eg. '.' and perform some actions. i am using @tableID as a passes parameter for the sproc.
12
2953
by: scott | last post by:
In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset. The below USAGE code will display my SPROC results. USAGE: EXEC...
1
2037
by: teddysnips | last post by:
SQL Server 2000 I have a stored procedure that uses an extended SPROC to send an email notification to my customers when a document is distributed. However, the SPROC has an unexpected side effect. If I run it in its current incarnation, it only sends one email and then exits. However, if I remove or comment out the block
1
5805
by: billa1972 | last post by:
Hi, I have a sproc with 5 params that takes about 40 seconds to return. But when I Create a Temp table and do a Insert Into #temp Exec sproc param1, param2, param3, param4, param5 it never returns...
4
2096
by: Radu | last post by:
Hi. It seems to be very simple, actually, but I don't know if it is feasible in TSQL. I have a sproc which gathers in one place many calls to different other sprocs, all of them taking a 'StoreGroupe' parameter. I would like to add a case where if the call has NO StoreGroupe parameter, the sproc should LOOP thru all records in table...
3
4827
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to generate a report. How do I set the Recordsource of the report to the result of the select sproc ? I have tried the following, but it does not...
1
2002
by: P | last post by:
Hello, I am having a difficult time updating a record via a stored procedure using the gridview and sqldatasource. I cannot seem to be able to find a way to set everything up so that I can pass the applicable fields in the gridview to a sqldatasource which in turn calls a sproc with the appropriate parameters and data. Any ideas? Thanks...
4
1820
by: Chad Micheal Lawson via .NET 247 | last post by:
I'm stumped at this point and I'm tired of trying things so I'mposting in hopes of some guru with a sharp eye. I have anasp.net app running on a local Win XP Pro box. Within the app,I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROCcreation and copy those values into Query...
0
1167
by: seevion | last post by:
My first post (beginner).. I hope it is clear enough and appreciate your taking the time to consider helping. I have an existing sproc that takes a cart transaction from a table and inserts shared values of that transaction into another table called Transaction_PT. The sproc works fine, it takes the total sale value and attributes profit share...
0
7450
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7720
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7957
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7470
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7809
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5368
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5088
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1941
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
763
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.