Loop through dataset and run a query for each row

[ABSTUBER]

I have a dataset of project numbers. I am interested in looping through the data and for each project querying a database. I could just concatenate them and create an in clause but there could be more than 1000 projects. Any ideas on how I could accomplish this?

[Prometheus · Accepted solution]

@ABSTUBER This is a standard use case for a batch macro. You could create your query in your Input Data tool or a Formula tool that goes to a Dynamic Input tool and connect a Control Parameter to it then save that as your batch macro. Make sure your Action tool is configured to replace the project number you start out with in your query. Then you take the list of project numbers and input them into that macro and you should get what you're looking for.

[ChrisTX]

One option we've used in the past....

  obtain security permission to create Temp tables in the database you're querying

  upload your project numbers to a DB Temp table, using In-Database tool Data Stream In

  use the tool Join-in DB to join from the Temp table to your "other" table

Chris

[apathetichell]

what I do:

1) identify the field you want which I assume you're using in a where clause.

2) figure out - are they text or numbers in your DB?

3) if they are text. conacetenate in summarize toold using 
' ',' ' as your 3 seperators. if number you will need to conver to text and the use  ,  as your seperators with , in the middle.

4) in a seperate part of your workflow write your standard query in a text field. use a summarize tool to combine lines if there are more lines. make sure you write your where clause with a value like WHERE project IN (test)

5) use a formula tool to create the name of your in-db connection. so like "Snowflake"

6) append your concatenated data to your other data using an append fields.

7) use a formula tool to replace test (or whatever is in your IN clause - with your concaetenated data.

8) attach a dynamic input in-db. Use your connection name from your formula tool. use your query you built from your text input field and your contactenated values.

[ABSTUBER]

@Prometheus That did the trick. Thank you!