asked on

East question on stored procedure

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

Saqib Khan

INSERt into table3 (tb2_id,name,age)
SELECT field1, field2, field3 FROM table2 where tb1_ID=5

Good Luck:)

rafrancisco

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?

jkain023

ASKER

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

jkain023

ASKER

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

jkain023

ASKER

Im not sure how to cancel this question. Can a moderator help me out, i kinda solved my question myself

rafrancisco

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

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

jkain023

ASKER

thx rafrancisco

ASKER CERTIFIED SOLUTION

PAQ_Man

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial