MS SQL - How to loop through table rows in T-SQL

In this post we will see three ways to iterate through table rows in a loop using T-SQL. I am using Microsoft SQL SERVER 2008 R2. Let first setup a target table we want to iterate rows. Create a new table (say Items) in database, with the following script.

--Create Table
CREATE TABLE [dbo].[Items](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Item_Code] [nvarchar](255) NOT NULL,
 [Item_Name] [nvarchar](255) NOT NULL,
)

Populate Items table with some dummy records.

SET IDENTITY_INSERT [dbo].[Items] ON
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (1, N'I001', N'Item1')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (2, N'I002', N'Item2')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (3, N'I003', N'Item3')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (4, N'I004', N'Item4')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (5, N'I005', N'Item5')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (6, N'I006', N'Item6')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (7, N'I007', N'Item7')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (8, N'I008', N'Item8')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (9, N'I009', N'Item9')
INSERT [dbo].[Items] ([Id], [Item_Code], [Item_Name]) VALUES (10, N'I010', N'Item10')
SET IDENTITY_INSERT [dbo].[Items] OFF

Using Cursor
This is the first way we are iterating table rows using CURSOR. For the scope of example I am using only top 10 records in these scripts, you can change according to your requirements. Here is the first loop:

  
--Using Cursor
DECLARE @MyCursor CURSOR;

--sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemCode varchar(255);
DECLARE @ItemName varchar(255);

BEGIN
    SET @MyCursor = CURSOR FOR
    select top 10 
    Id, Item_Code, Item_Name 
    from dbo.Items

    OPEN @MyCursor
    FETCH NEXT FROM @MyCursor
    INTO @ItemID, @ItemCode, @ItemName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    
  --Your logic here...
  --just printing in the loop
  print 
   'ItemID=' + Cast(@ItemID as varchar(255)) +
   ', ItemCode=' + @ItemCode + 
   ', ItemName=' + @ItemName   
   
   
  FETCH NEXT FROM @MyCursor
  INTO @ItemID, @ItemCode, @ItemName      
  
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

Fetching the desired records in cursor and check in the while loop until we get @@FETCH_STATUS = 0. Inside the loop you can put your logic what you want to do with each row's contents. In this example I am using three variables ItemID, ItemCode and ItemName and populating their values from each row content. Then I am just printing these to see the results.

Using temp table (using unique integer key)
Second method is to use temp table that already have a column with unique int key.

--Using #tempTable while target table has an int Unique key
select top 10 
Id,Item_Code, Item_Name
into #tempTable 
from dbo.Items

--sample variables to hold each row's content
DECLARE @ItemID int;
DECLARE @ItemCode varchar(255);
DECLARE @ItemName varchar(255);

while exists (select * from #tempTable)
begin

    select top 1 
     @ItemID = Id
    ,@ItemCode = Item_Code
    ,@ItemName = Item_Name
    from #tempTable
    order by Id asc

    --Your logic here...
 --just printing in the loop
 print 
  'ItemID=' + Cast(@ItemID as varchar(255)) +
  ', ItemCode=' + @ItemCode + 
  ', ItemName=' + @ItemName 


    delete #tempTable where Id = @ItemID

end

drop table #tempTable

In this technique, we are extracting the desired records in a temp table. In the while condition we are checking if there is any record exists in this temp table then continue looping, and within the loop we are selecting top 1 single record, extract its content, put our logic, and then in the end, we can simply delete the current row from temp table. So finally we will get all rows processed and deleted from temp table.

Using temp table (adding new identity key)
Third technique is to use temp table, and add an IDENTITY field in temp table, to uniquely identify each row.

--Using #tempTable and have created own Identity ID
SELECT 
TOP 10
IDENTITY(INT, 1,1) AS TempID,
Item_Code, Item_Name
INTO #tempTable
FROM dbo.Items 

--sample variables to hold each row's content
DECLARE @ItemCode varchar(255);
DECLARE @ItemName varchar(255);

--helper variables used for looping purpose
DECLARE @MaxTempID int;
DECLARE @CurrentTempID int;

SELECT @MaxTempID = MAX(TempID) from #tempTable
SELECT @CurrentTempID = MIN(TempID) from #tempTable

WHILE @CurrentTempID <= @MaxTempID
BEGIN

    -- Get one record
    SELECT TOP 1 
    @ItemCode = Item_Code
    ,@ItemName = Item_Name
    FROM #tempTable
    WHERE TempID = @CurrentTempID

     --Your logic here...
 --just printing in the loop
 PRINT
  'ItemCode=' + @ItemCode + 
  ', ItemName=' + @ItemName 

    SELECT @CurrentTempID = @CurrentTempID + 1
END

In this method, before starting loop we extract the maximum and minimum values of identity column(that we added) in variables to use for looping conditions. Inside the loop we are extracting each row's content by that unique identity value.