Breakdown a list of IDs into batches and run SQL query

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!

Datasets

Database Connection

Accepted answers

OllieClarke

@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.yxmd

All comments

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

@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.yxmd

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 🙂

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703