simple foreach through a table

Select * into #temp from dbo.table

We know that one – now, how to do something for each row in the table?

consider this:

declare @column1 varchar(10)
select top 1 @column1 = column1 from #temp order by tableName
-- if there are no rows, @@rowcount is zero
while (@@rowcount > 0)
begin
-- do something with the selection
print column1
-- delete the entire row we just selected
 delete from #temp where column1 = @column1
 select top 1 @column1 = column1 from #temp order by column1
end

What we are doing is selecting one row from #temp.  I am attempting to maintain the same order each time by the order by, but for the purposes of this post, it doesn’t matter.  We do something with the row data.  We delete from #temp that row we just selected.  We select another row from the diminished table.  Assuming @@rowcount is still positive, we do it all over again.    Because of the ‘while’ we have to do the first selection outside, and we have to check the new value of the loop at the bottom.  Or else after the last row is deleted, @@rowcount would be 1, we would go to the top and enter the loop again based on the last selection success.  Or actually, the last deletion.

Advertisements