Batch Macro + Paramaterize SQL Statement?

[Philip_Burrow]

Using IN-DB tools, I’m trying to create a process that queries the same database table multiple times, just swapping out a field for each run.  Each time it runs, it appends the results to a second table.

I think I can accomplish this by parameterizing the SQL query (creating a parameter for the field, then swapping field), but I’m not sure how to do that IN-DB.  I’ve looked into using Dynamic Input IN-DB but it doesn’t like any connection string I give it.  I’ve also looked into using the transpose tool, but this particular data set is 8M records by 180 fields, so using transpose murdered the database.

Is there a way to parameterize a field name SQL query, then use a batch macro to run the SQL query x number of times, one for every parameter value?

For example:

Say this is my data structure for [testTable], where 1 means a member has visited a state, and 0 means they haven’t. I want to flip this from a wide to a tall table, keeping only the records where a member has visited a state.

Name	DOB	MN	TX	WA	CA	AZ
MEMBER A	1984-06-04	1		1
MEMBER B	1988-05-01		1			1
MEMBER C	1963-03-21	1			1
MEMBER D	1965-06-04	1	1			1
MEMBER E	1983-01-18	1
MEMBER F	1982-12-19		1	1	1
MEMBER G	1936-07-06				1	1

SELECT * from [testTable]

WHERE [MN] IS NOT NULL

Insert results into tableB

SELECT * from [testTable]

WHERE [TX] IS NOT NULL

Insert results into tableB

SELECT * from [testTable]

WHERE [WA] IS NOT NULL

Etc..

Til the final table looks like this

Member	DOB	State	Visit Flag
MEMBER A	1984-06-04	MN	1
MEMBER C	1963-03-21	MN	1
MEMBER D	1965-06-04	MN	1
MEMBER E	1983-01-18	MN	1
MEMBER B	1988-05-01	TX	1
MEMBER D	1965-06-04	TX	1
MEMBER F	1982-12-19	TX	1
MEMBER A	1984-06-04	WA	1
MEMBER F	1982-12-19	WA	1
MEMBER C	1963-03-21	CA	1
MEMBER F	1982-12-19	CA	1
MEMBER G	1936-07-06	CA	1
MEMBER B	1988-05-01	AZ	1
MEMBER D	1965-06-04	AZ	1
MEMBER G	1936-07-06	AZ	1

Any help would be appreciated!

[mshanton]

Hey @AmeliaG

First of all, thanks so much for your help... 

Here is a screen shot comparing our Action Tool XML... so you can see the difference...

I just quickly created a Macro, (attached) and after creating it.... it did not add the "/File" after my destination xml logic.

Thanks, Mike

[dineshp]

Hi @AmeliaG @Philip_Burrow @mshanton 

I am using alteryx v11.7.4.4 and i am also having the same problem. I had to edit the XML file and include "/File" into <Destination>1/File</Destination> to get the macro working. Anyone know why? 

[spandan92]

Hi @AmeliaG

I am trying to create a Macro in similar lines. Below is my situation, Kindly help:

 1. There are multiple databases

 2. But SQL query to extract data is same

So, I wanted to build a Drop-Down tool to select the desired database and then corresponding SQL would work and give the output. Kindly help in formulating the workflow.

Thanks!!