i'm new to sqlserver. is there a way to step throught each row returned in select statement. what i have is a number of rows inserted by a bulk insert into a staging table. from there i need to insert each row x # of times into another table depending on the 'quantity' column in the staging table.
You almost never need to "step through" a table in SQL Server, and it is rarely an efficient method of processing. In this case you can solve your problem by
creating a table of sequential numbers 0 through MAX REQUIRED ROWS.
Then structure an INSERT query like this:
Insert into PRODUCTIONTABLE
where SEQUENTIALNUMBERTABLE.VALUE < STAGINGTABLE.QUANTITYCOLUMN
If it's not practically useful, then it's practically useless.
You may also find it most efficient to do the overall job in smaller stages, committing a transaction each time.
For example, let's say that your bulk-input consists of 1,000 rows. If you perform the insert operation (by whatever means) as "10 groups of 100 rows each" or even "100 groups of 10," the total amount of "undo-load" on the system remains much smaller. The total amount of resources demanded by each query while in-progress is much smaller, and so, its performance impact on the system is much smaller.