August 11, 2012 at 8:56 am
I need to make a stored procedure that looks at table 1 and if there is a row set that has a status of P then I need to select some values from the table and insert the values into 3 other tables and change the status from table 1 to C and move to the next row in table 1 and do the same.
I can successfully do it for the first row but cannot make it go to the next row until there are no more rows with a status of P. How can I make it keep looking through the table/rows and writing the data for every row until there are no more rows with a P status?
HEEELP
August 11, 2012 at 10:47 pm
August 13, 2012 at 3:56 am
You almost certainly don't need a loop (and it would probably be a very bad idea to do so). I suspect that something like this will do the trick:
INSERT Table2
SELECT [RequiredFields]
FROM Table1
WHERE Status = 'P'
INSERT Table3
SELECT [RequiredFields]
FROM Table1
WHERE Status = 'P'
INSERT Table4
SELECT [RequiredFields]
FROM Table1
WHERE Status = 'P'
UPDATE Table1
SET Status = 'C'
WHERE Status = 'P'
Depending on the size of your tables and the selectivity of the Status field it might be better to do a single select into a temp table and then write to the 3 output tables from there.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply