Update SQL Schema in Multiple Input Tools

[mholland]

Hi,

I have a database with multiple tables from different data sources. I have one database schema set up for each of my Clients.

I'd like to be able to amend the database schema in one tool, for it to then pass down into the SQL statements in the multiple Input tools I have. This way I can swap over the Alteryx Workflow easily without having to update each Input tool.

As an example:

Input for DV360 = SELECT * FROM CLIENT SCHEMA.ggldv360bm_omgf_base_ads

Input for CM360 = SELECT * FROM CLIENT SCHEMA.googlecm_omgf_base_ads

I'd like to have a tool that would replace "CLIENT SCHEMA" an all Input Tools.

Can you tell me how I can achieve this?

Thanks,

Mark

[apathetichell]

If these are in-db connections - I'd recommend setting up a batch macro with a text input box - and using dynamic input in-db. If these are not in-db you probably want to do something similair replacing queries in an input data tool. You would use at least one batch macro - if not a nested outer/inner batch macro structure to control the SQL being passed in.

[mholland]

Hi @apathetichell ,

Can you show me an example of how to set this up? I'm not sure of the process.

Thanks,

Mark

[apathetichell]

are you using in-db tools?

[mholland]

No, I was using a simple input tool to bring in my data from Redshift, using ODBC connections.

[mholland]

But I could swap to IN-DB if that makes things easier/more efficient?