Execute a large list of queries and union results together

[dataguy4life]

I'm trying to make a file with 3 non-null samples from every field in a database. I have a long list of queries that would generate the results, and I am hoping I can automate by having Alteryx execute the large list of queries and union the results together. Below are an example of the input and output I'm hoping for.

I already have my input (list of queries), so that's not an issue. I can successfully execute one query at a time. How can I execute in bulk and create the output?

INPUT:

QueryID	Query
1	SELECT [Field1] as value FROM [SCHEMA].[Table1] WHERE [Table1].[Field1] is not null limit 3
2	SELECT [Field2] as value FROM [SCHEMA].[Table1] WHERE [Table1].[Field2] is not null limit 3
3	SELECT [Field3] as value FROM [SCHEMA].[Table2] WHERE [Table2].[Field3] is not null limit 3
4	SELECT [Field4] as value FROM [SCHEMA].[Table2] WHERE [Table2].[Field4] is not null limit 3
...	...
10000	SELECT [FIELD10000] as value FROM [SCHEMA].[Table95] WHERE [Table95].[Field10000] is not null limit 3

OUTPUT:

QueryID	ValueID	Value
1	1	A
1	2	B
1	3	C
2	1	11/1/22
2	2	11/2/22
2	3	11/3/22
3	1	John
3	2	Vanessa
3	3	Mary
4	1	97489
4	2	83739
4	3	28956
...	...	...
10000	3	ABC

[DataNath]

Hey @dataguy4life, I notice that you've used the 'Batch Macro' tag in your post, which is exactly what I'd suggest here. You'd feed in your list of queries to the control parameter, which will update the query that sits in the Input tool within the macro itself. What I would say is that you'll need to force the data type of the [Value] data into a uniform type (String: Forced) with a Select tool before the macro output so that when the batch macro has ran, the results can be unioned and there's no schema error from the varying data types coming through from the queries (dates/integers/strings etc).

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

[gabrielvilella]

I believe a batch macro could help you on this. The macro will run one time for each query and then all results will be on a single table. 

[Felipe_Ribeir0 · Accepted solution]

Hi @dataguy4life 

I was able to replicate your situation here for SQL Server using the attached workflow.

1)Just configure the input of queries correctly

2)Inside the macro, change the input file connecting it to a valid server/database/table

3)Inside the update tool, configure it to replace the query of the item 2.

And it will work properly for sure.

[dataguy4life]

This worked!!! Thank you so much!!!!!

[dataguy4life]

Hi DataNath, this was a really helpful tip about the data type. Thank you so much!!!