SQL Server loop - how do I loop through a set of records
By using T-SQL and cursors like this :
DECLARE @MyCursor CURSOR; DECLARE @MyField YourFieldDataType; BEGIN SET @MyCursor = CURSOR FOR select top 1000 YourField from dbo.table where StatusID = 7 OPEN @MyCursor FETCH NEXT FROM @MyCursor INTO @MyField WHILE @@FETCH_STATUS = 0 BEGIN /* YOUR ALGORITHM GOES HERE */ FETCH NEXT FROM @MyCursor INTO @MyField END; CLOSE @MyCursor ; DEALLOCATE @MyCursor; END;
https://*.com/questions/28506747/sql-loop-through-each-row-in-a-table
Based on the caption of your question. This is the way I loop through each row of a table using a variable of type TABLE
:
DECLARE @counter INT = 1, @max INT = 0 -- Declare a variable of type TABLE. It will be used as a temporary table. DECLARE @myTable TABLE ( [Id] int identity, [Column1] nvarchar(max), [Column2] nvarchar(100) ) -- Insert your required data in the variable of type TABLE INSERT INTO @myTable SELECT Column1, Column2 FROM [dbo].[YOUR_DATABASE_TABLE] -- Initialize the @max variable. We'll use thie variable in the next WHILE loop. SELECT @max = COUNT(ID) FROM @myTable -- Loop WHILE @counter <= @max BEGIN -- Do whatever you want with each row in your table variable filtering by the Id column SELECT Column1, Column2 FROM @myTable WHERE Id = @counter SET @counter = @counter + 1 END
实例
DECLARE @TempTable TABLE ( RowNumber INT , MemberId INT , Birthday DATETIME ); INSERT INTO @TempTable ( RowNumber , MemberId , Birthday ) SELECT ROW_NUMBER() OVER ( ORDER BY MemberID ASC ) , MemberID , Birthday FROM dbo.vie_mem_16 WHERE Birthday IS NULL; SELECT * FROM @TempTable; DECLARE @CurrentDate DATETIME = '20180604'; DECLARE @CurrentRowNumber INT = 1; DECLARE @MaxRowNumber INT; DECLARE @TempMemberId INT; SELECT @MaxRowNumber = MAX(RowNumber) FROM @TempTable; WHILE ( @CurrentRowNumber <= @MaxRowNumber ) BEGIN SELECT @TempMemberId = MemberId FROM @TempTable WHERE RowNumber = @CurrentRowNumber; UPDATE dbo.tbm_mem_Member_Beneficiary SET Birthday = @CurrentDate WHERE MemberID = @TempMemberId; SET @CurrentRowNumber = @CurrentRowNumber + 1; SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); END;