I have a execute SQL statement action in which I loop through as many times necessary to retrieve all data from a SQL table. In the same loop I want to add each result set to a collection so I can then check and see if it already exists in the list. If it does not I add it to the list. My problem is this, it seems the collection is only ever getting the last set of results from the final pass of the loop.
Example: I have 400 records I need to return from a external database table. I return 200 at a time(limitation of execute SQL), lets say the set is just the numbers 1 - 400. So the first pass I return 1- 200, the second pass I will return 201 - 400. When the workflow completes the collection only contains the values 201- 400. So my question is this, how do I set this up so that the collection is not wiped out every loop and only accumulates and adds the items to the collection. I have attached a screenshot of my current workflow setup.
Thanks in advance.