Can I loop through a table variable in T-SQL?

Question

Is there anyway to loop through a table variable in T-SQL?

DECLARE @table1 TABLE ( col1 int )   INSERT into @table1 SELECT col1 FROM table2 

I use cursors as well, but cursors seem less flexible than table variables.

DECLARE cursor1 CURSOR       FOR SELECT col1 FROM table2   OPEN cursor1   FETCH NEXT FROM cursor1 

I would like to be able to use a table variable in the same manner as a cursor. That way I could execute some query on the table variable in one part of the procedure, and then later execute some code for each row in the table variable.

Any help is greatly appreciated.

Answer 1

Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT.

Try this:

DECLARE @RowsToProcess  int DECLARE @CurrentRow     int DECLARE @SelectCol1     int  DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )   INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT  SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN     SET @CurrentRow=@CurrentRow+1     SELECT          @SelectCol1=col1         FROM @table1         WHERE RowID=@CurrentRow      --do your thing here--  END 

Answer 2

DECLARE @table1 TABLE (     idx int identity(1,1),     col1 int )  DECLARE @counter int  SET @counter = 1  WHILE(@counter < SELECT MAX(idx) FROM @table1) BEGIN     DECLARE @colVar INT      SELECT @colVar = col1 FROM @table1 WHERE idx = @counter      -- Do your work here      SET @counter = @counter + 1 END 

Believe it or not, this is actually more efficient and performant than using a cursor.