I know it help it says update your query to retrieve the next 200 records, how do you set a loop to reach the end of record count?
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 ,OrderID ,OrderDate ,CustomerID ,EmployeeID FROM dbo.Orders)
SELECT * FROM OrdersRN 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?
Damien Dolan 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.
I get the logic and have it working in SMSS but haw can I pass the increased variable to a variable inside my execute SQL statement?
Retrieving data ...