Workaround for List Limit of 500 for SQL IN Query

I’d like to query transaction details for a list of purchase orders in our SQL Server database. However, whenever my list exceeds 500 items, I get an “Execute failed: I/O Error: Connection reset”. Any ideas on a workaround?

Here’s an image of the workflow I’m using. First I past in the list of PO’s I’d like to research. The GroupBy Unique Concatenate translates it into a list that I pass to a flow variable that I use in my query that looks something like this:

select *
from TABLE
where PONumber in ($${SPOList}$$)

@stevelp this might be a job for:

2 Likes

Hi @stevelp , I would not want to pass 500 items in an IN statement.

The Parameterized DB Query Reader is probably the most relevant node to use in this case.

Alternatively, you could load your IN items into a temporary table to your DB system, and then join your table with this temporary table. This would be a faster solution if you had a huge amount of items to filter on, especially that you could index that column in your temporary table.

But for 5k, 6k, even up to 7k of items (records), you could easily use the Parameterized DB Query Reader

4 Likes