SQL Server For Each Row Next

It is difficult for me to write this particular article and I’ll tell you why. If you don’t care then just skip down to the example, but here goes anyway. It is very rare that you should have to perform looping in SQL. There are certain situations that do require it, and they are mostly back-end or maintenance related.

Ok, now that I got that off my chest, here you go. In order to perform an execution which iterates performing a “for each row”, this is the easiest way for me:

[cc lang=”sql”]
SELECT
RowNum = ROW_NUMBER() OVER(ORDER BY CustomerID)
,*
INTO #Customers
FROM SalesLT.Customer

DECLARE @MaxRownum int
SET @MaxRownum = (SELECT MAX(RowNum) FROM #Customers)

DECLARE @Iter int
SET @Iter = (SELECT MIN(RowNum) FROM #Customers)

WHILE @Iter <= @MaxRownum BEGIN SELECT * FROM #Customers WHERE RowNum = @Iter -- run your operation here SET @Iter = @Iter + 1 END DROP TABLE #Customers [/cc] Once again, take heed, even though this is how to loop, I encourage set-based operations whenever possible.

18 comments
Roel 05 Sep 2016 at 9:17 pm

Thank you so much

Richard 07 Aug 2015 at 4:11 am

Good job!!!! Thanks.

NAIDU GARU 18 Jun 2015 at 11:50 am

i need more explanation

Yates 27 Feb 2015 at 2:58 pm

Thanks for this post. This led me to solving a UPDATE issue on one of my tables.

Juan 29 Sep 2014 at 7:53 pm

Question!, I wanna update my table, but change all the rows at same,!,
I change SELECT *
FROM #Customers
WHERE RowNum = @Iter
for this
update A set AD = @Iter from #Customers where RowNum = @Iter

Can you help me, please!??

Rohit 08 Jul 2014 at 2:06 pm

Thanks 🙂

LEBON INIYAVAN 21 May 2014 at 5:52 am

Thanks! That was really helpful!

Jason L 19 Feb 2014 at 8:47 pm

Thanks, that was helpful.

Keith Beckman 27 Mar 2013 at 4:57 pm

Why have you reinvented the cursor? If you absolutely MUST do iterative T-SQL, at least use a cursor, which is optimised for the purpose.

Derek Dieter 08 Apr 2013 at 2:08 am

Keith, I’ll have to agree with you here for the most part. Use a cursor first. I’ll have to update this article.

A Roy Chowdhury 03 Feb 2012 at 6:24 am

Hi all,
Please suggest me what will be best way to processing 1000000 records in following logic..

Begin Loop on Master Table
Posting on Transaction File
Begin Loop on Detail Table
Posting on Transaction File
Loop
Loop
—————————————————
Posting Procedure on Transaction File
If record not exits in Transaction File
Begin
Insert New Record
End
Else
Begin
Update Same records
End

– Regrds
A Roy Chowdhury

Anonymous 03 Feb 2012 at 6:19 am

Begin Loop on Master Table
Begin Loop on Detail Table

Loop

Doc 29 Jan 2012 at 5:43 pm
Doc 29 Jan 2012 at 5:38 pm

Thanks. Solved my problem!

David 15 Dec 2011 at 1:20 pm

Why not:
Declare @field1 varchar(254), @field2 varchar(254)

Declare COUNTER Cursor for
Select field1,field2 from Table
open COUNTER
fetch next from COUNTER into @field1,@field2
while @@fetch_Status != -1
begin

–process code

fetch next from COUNTER into @field1,@field2
end
close COUNTER
Deallocate COUNTER

Somayy 26 Oct 2011 at 11:16 pm

Hi
I Want update fields of my table
WHERE (Root_id = 112) OR
(Root_id =(SELECT Id FROM tb_Topic WHERE (Root_id = 112)))

but becuse “(SELECT Id FROM tb_Topic WHERE Root_id= 112))”
have several amount not be execute;
what do i do?

Mehnrnoosh 08 May 2011 at 10:15 pm

Hi
I Want found ID for every Update in sql

reaperbv 21 Apr 2011 at 1:40 am

Can We use:

SELECT ExecuteFunction(*)
FROM #Customers

CREATE FUNCTION ExecuteFunction
(
@p_Value1 FLOAT,
@p_Value2 FLOAT
) RETURNS FLOAT
BEGIN
— run your operation here
END

Featured Articles

 Site Author

  • Thanks for visiting!
css.php