Breakdown a list of IDs into batches and run SQL query

[murthybhs]

Hello amazing Alteryx community,

I have a data set that has over 200k rows. Running SQL query on all of them together is taking too long which is leading to the query getting killed by the DBA. I would like to create a workflow where I can split the 200k rows as 10 rows each as batches and run the SQL query and move on to the next 10 until the query is run on all of them. I would like to output the results of all 200k rows IDs as a single file.

Any help in this is highly appreciated.

Thank you so much!

[dYoast]

@murthybhs - What type of field is ID?  Is it unique?  Is it autonumber?  Does the table continue to grow?

If it is unique, you might be able to do what you want with an iterative macro.

[murthybhs]

ID is a unique field @dYoast. There are 200k rows for May 2021 data. There might be more or less IDs for other months. The data does not grow. It is fixed.

[OllieClarke · Accepted solution]

@murthybhs if you can get your IDs out of your table, then you could use a workflow like below to create your groups

These groups could then be fed into a dynamic input tool to update a WHERE clause so you're only inputting 10 IDs at a time.

You might need to tweak the summarize tool depending on the SQL syntax you need

Hope that helps

[murthybhs]

Thank you for the help @OllieClarke. Very much appreciate it. I am having trouble replacing the SQL query with my batches. I would like the ('X') marked in yellow to be replaced with Concat_PRO. How do I accomplish that? I am trying to do it with Replace a Specific String: ('X') functionality in the Dynamic Input tool. It does not seem to work as I am not getting any output.

Ignore the error in the Dynamic Input

Your help is highly appreciated.

[OllieClarke]

@murthybhs firstly I'm not sure the error in the dynamic input tool is irrelevant, but beyond that, the replace a specific string should exactly match your SQL query. If your screenshot is correct then you have multiple newlines (and possibly other white space) between your brackets and the 'X'. If the error is irrelevant, then changing your SQL query so it says 

in ('X')

should help.

Ollie

[murthybhs]

It worked. Thank you so much for the help. Appreciate it.

[OllieClarke]

@murthybhs glad I could help 🙂