To copy multiple tables like 50-70 tables from one database to another database

[GayathriNagarajan]

I want to do data copy of multiple tables close to 70 tables from one database to another database. It is not straight copy rather, I want to copy selected data using WHERE conditions for every table.

Note: Only the WHERE condition parameter is same for all the tables. The "Select" query differs, WHERE condition column also differs.

Below is a screenshot of what exactly I am doing. Every input and output pair is for copying one table.

In this case, I want to apply WHERE condition parameter to all tables. Eg WHERE COLUMN IN (XXX)

Currently the value of XXX is configured in Alteryx Constants it is working fine. 

But I want the value from an excel file and then pass the same here.

Is there a way to do it? I tried macros/analytic apps, but updating every tool doesn't seem good and makes it clumsy. 

So does Alteryx has any provision for this scenario?

[danilang · Accepted solution]

Hi @GayathriNagarajan 

The general technique would be to store the SQL text of each query as well as the output table name in the excel file. 

1. Read the excel file.

2. Replace the where clause using a Formula tool

3. Create batch macro that contains an Input tool with a dummy SQL string and an output tool

4. Pass the modified SQL string as one control parameter and the output table name as another control parameter.

5. Connect the SQL control param to the input tool and use the action tool to replace the dummy SQL with current one

6. Connect to the output table name control param to the output tool and replace the output table name.

Dan 

[MarqueeCrew]

@danilang ,

i like your style.  I also suggest adding a save to YXDB inside of the macro.  Keep a copy local just in case. 

cheers,

 mark

[GayathriNagarajan]

Thanks a lot! This is much cleaner implementation and I did it.