?
Solved

East question on stored procedure

Posted on 2005-05-16
9
Medium Priority
?
189 Views
Last Modified: 2010-08-05
I am trying to copy a record within a table. So for each record returned create a new record. i.e.

SELECT * FROM table2 where tb1_ID=5  ->> returns some rows: with columns (tb2_ID, tb1_ID, name)

what i want to do is: for each row returned i want to create a record such that:
INSERT INTO table3 (tb2_ID,name, age) values (...)  ->> the values tb2_ID is the id from the above SELECT

so if the first select returned:
1,5,'test'
2,5,'meh'
3,5,'blah'

i should do 3 inserts as follows:
INSERt into table3 (tb2_id,name,age) values (1,'test',23)
INSERt into table3 (tb2_id,name,age) values (2,'meh',16)
INSERt into table3 (tb2_id,name,age) values (3,'blah',56)


My question is how do i loop through the returned records and get the tb2_ID.
This is has to be a stored procedure. thx
0
Comment
Question by:jkain023
8 Comments
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 14011262
INSERt into table3 (tb2_id,name,age)
SELECT field1, field2, field3  FROM table2 where tb1_ID=5



Good Luck:)
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14011266
Try this:

INSERT INTO Table3 (tbl2_id, name, age)
SELECT tbl2_id, name, <Age Column>
FROM table2
WHERE tbl1_id = 5

By the way, where are you going to get the value for the age column?
0
 
LVL 2

Author Comment

by:jkain023
ID: 14011420
Let me give u the details to what i am doing:
ps. all IDs are defined as identities....

table1
------
ID1, other fields

table2
-------
ID2, ID1, other fields (ID1 foreign key)

table3
-------
ID3, ID2, other fields(ID2 foreign key)

I need to replicate a record in table1 as a new record and its other records in other tables that are linked

I am done replication for table1 ->insert into (fields) SELECT fields FROM table1 where ID1=[some val]
I am done replication for table2 ->insert into (ID1,fields) select ID1[from above insert],fields from table2 where ID1=[from the initial record in table1]

now here is where i die out.
table2 can have many records and each record in table2 can have many records in table3

So esentially i need to loop though each record in table2 and create all the corresponding records in table3


 


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:jkain023
ID: 14011607
neva mind guys...
i got hold of it

I can use a cursor for my records returned and loop through them to execute my sqls for th table3
below is a sample operation flow for looping through recordsets


                declare @CustId nchar(5)
      declare @RowNum int
      declare CustList cursor for
      select top 5 CustomerID from Northwind.dbo.Customers
      OPEN CustList
      FETCH NEXT FROM CustList
      INTO @CustId
      set @RowNum = 0
      WHILE @@FETCH_STATUS = 0
      BEGIN
        set @RowNum = @RowNum + 1
        print cast(@RowNum as char(1)) + ' ' + @CustId
        FETCH NEXT FROM CustList
          INTO @CustId
      END
      CLOSE CustList
      DEALLOCATE CustList


0
 
LVL 2

Author Comment

by:jkain023
ID: 14011615
Im not sure how to cancel this question. Can a moderator help me out, i kinda solved my question myself
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14011637
Here's a link to what you can do since you answered your own question:

I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
 
LVL 2

Author Comment

by:jkain023
ID: 14011780
thx rafrancisco
0
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 14057598
Question Closed, 125 points refunded.
PAQ_Man
Community Support Moderator
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question