Can I loop through a table variable in T-SQL?
Solution 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
Solution 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.
Solution 3:
My two cents.. From KM.'s answer, if you want to drop one variable, you can do a countdown on @RowsToProcess instead of counting up.
DECLARE @RowsToProcess 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
WHILE @RowsToProcess > 0 -- Countdown
BEGIN
SELECT *
FROM @table1
WHERE RowID=@RowsToProcess
--do your thing here--
SET @RowsToProcess = @RowsToProcess - 1; -- Countdown
END
Solution 4:
You can loop through the table variable or you can cursor through it. This is what we usually call a RBAR - pronounced Reebar and means Row-By-Agonizing-Row.
I would suggest finding a SET-BASED answer to your question (we can help with that) and move away from rbars as much as possible.
Solution 5:
look like this demo:
DECLARE @vTable TABLE (IdRow int not null primary key identity(1,1),ValueRow int);
-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------
DECLARE @cnt int = 1;
DECLARE @max int = (SELECT MAX(IdRow) FROM @vTable);
WHILE @cnt <= @max
BEGIN
DECLARE @tempValueRow int = (Select ValueRow FROM @vTable WHERE IdRow = @cnt);
---work demo----
print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
print '@cnt:' + convert(varchar(10),@cnt);
print'';
--------------
set @cnt = @cnt+1;
END
Version without idRow, using ROW_NUMBER
DECLARE @vTable TABLE (ValueRow int);
-------Initialize---------
insert into @vTable select 345;
insert into @vTable select 795;
insert into @vTable select 565;
---------------------------
DECLARE @cnt int = 1;
DECLARE @max int = (select count(*) from @vTable);
WHILE @cnt <= @max
BEGIN
DECLARE @tempValueRow int = (
select ValueRow
from (select ValueRow
, ROW_NUMBER() OVER(ORDER BY (select 1)) as RowId
from @vTable
) T1
where t1.RowId = @cnt
);
---work demo----
print '@tempValueRow:' + convert(varchar(10),@tempValueRow);
print '@cnt:' + convert(varchar(10),@cnt);
print'';
--------------
set @cnt = @cnt+1;
END