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:
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
Once again, take heed, even though this is how to loop, I encourage set-based operations whenever possible.
Popular search terms:
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
Begin Loop on Master Table
Begin Loop on Detail Table
Loop
Thanks!
Thanks. Solved my problem!
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
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?
Hi
I Want found ID for every Update in sql
Can We use:
SELECT ExecuteFunction(*)
FROM #Customers
CREATE FUNCTION ExecuteFunction
(
@p_Value1 FLOAT,
@p_Value2 FLOAT
) RETURNS FLOAT
BEGIN
— run your operation here
END