Suppose I have a table amountInfo with columns (id, amount1, amount2, amount3) where amountX are money values and id is an int value ranging from 1 to some int under 10.
Currently I have some code that approximately does this:
declare @id int, @idmax int, @amounttotal money
select @idmax = (select max(Id) from amountInfo)
while (@id <= @idmax)
begin
select @amounttotal = sum(amount1 + amount2 + amount3)
from amountinfo where id=@id
-- do something with @amounttotal
select @id=@id+1
end
Is there a way to factor out the while loop with some kind of complex select statement here? I am experienced with C/C++/C# programming but SQL is kinda new to me. Thanks!
EDIT: basically the "--do something" part involves inserting the individual @amounttotals into another table. The actual query I'm running is more complicated than that but I'm hoping to solve this simple problem first before posting a huge example of code.