East question on stored procedure
Posted on 2005-05-16
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:
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