Input data from varying tables in a SQL Database

bluebrent

Hello all -

New Alteryx user here, so sorry if this is has been asked already.

I am working on a program that will be run every year.  One of the jobs requires about 10 data pulls from different tables on 1 SQL server every year.  So every year, these 10 tables will be different, and so I am looking for a way to do an input data tool on a dynamic table name.

Ideally, these tables would be defined as constants, or there would be a prompt asking the user to input the names of the tables.  I want to avoid having the user open an input tool and manually map to the new table.

can anyone offer any help on this?

Thanks much

CharlieS

This is exactly the sort of scenario the Dynamic Input tool was designed for. 

https://help.alteryx.com/2018.2/DynamicInput.htm

In your workflow, prepare the table name as a variable. Drop a Dynamic Input tool on the canvas and use the "Edit..." button and configure the query exactly as you would in a regular Input tool. Once that's done, go back to the Dynamic Input configuration and select the "Modify SQL Query" button and "Add" a "Replace a Specific String" option. Then edit the "Text to replace" so it shows the table current referenced in the query and select your table name variable to replace it with.

 

As for how you design the table name input, that's up to you if you'd like to define formula or user inputs to update those values.