ROW_NUMBER() seems to work:
Paging with ROW_NUMBER()
DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate,OrderID;
I'm sorry I should rephrase my question. I need to retrieve around 6,000 records that are unique identifiers for our customers. How can I loop through to get all 6,000 records?
so if I understood you correctly, what you need is not to loop through the records using Execute SQL action, but you want to get them all at once and the iterate through the collection? If so, then just save output of the Execute SQL into a collection and then do "For Each" to iterate through it.
That is correct but how can I retrieve all records if the max results a execute sql function will return is 200? That's where I am getting stuck. Thanks for your response!!!!!
Do it in a loop then, using suggestions from Fernando and the "Row_Number" - in each loop get next 200 rows, then evaluate if query returned anything, in case yes - process them and then increase the "PageNum" variable. PageSize in that case is 200.