This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am very new to Alteryx therefore please accept my apologies if this has already been answered, I have not however been able to find anything on this excellent community that gives me the answer or guidance I need.
My question is this:
How can I cycle through a list of table names from a list (say in Excel) which represent the table names within a source database, and subsequently create a corresponding table in a different, target database? I have over 100 tables within the list and is therefore far too cumbersome to create individual inputs for each table.
e.g. I have a list of table names which I need to pull through to a target database (held in excel) - example file attached.
I need to create a corresponding table, with a prefix and suffix attached to each table name ("SRC_" and "_EBS") respectively, within a different target database.
Target Database Corresponding Table Names
Many thanks in advance for any answers and guidance.
If the list of tables all contain the same schema, you can use the Dynamic Input tool which can be configured to modify your SQL statement replacing a specific part of the with data that you feed in. This tool would cycle through each value holding the results in each in memory and then output a single data set, similar to what a Union would do. As mentioned, this does require that the schema on each table match. Otherwise, the tool will skip any tables that do not match.
Alternatively, if there may be schema mismatches, then you'd have to create a batch macro that would loop through each individually. The reason why a batch macro would work is because there's an option that allows you to specify to ignore schema mismatches.
There a lot of articles/post that cover batch macros and suggest reviewing those is this is the route you'll have to take.
Here's an example. And correct, as @LordNeilLord mentioned... i was referring to table schema, not DB schema. Each table that list want this to read in would need to have matching field names/types.
In the attached example, my template input is simply "SELECT * FROM TablePlaceholder". The method i selected in the Dynamic Input tool is to replace a specific string, which i have it set to look for "TemplatePlaceholder" and loop through each value from the input connection.
Sorry for the delay in getting back to this, other matters got in the way.
Just wanted to pass on my appreciation for both of your time - the offered solution definitely worked and made me think about how to minimise the amount of processing done on the Alteryx Designer host.
I have attached an updated solution as I wanted to give something back the community. It isn't a perfect solution however the processing is mainly done In-DB and the main benefit being the Write Data In-DB can now be passed dynamic table names.
If anyone has any ideas on how the improve this further I would welcome the challenge and discussion.
For example I am now trying to figure out how to have the In-DB tools check whether or not a table already exists and if it does then dynamically change the creation mode to either append the data or drop the table.
Stage 0 - Normalised Data Extract and Load to Target Database.yxzp
I downloaded your batch example. However, I am having a bit of trouble configuring it for my case.
Basically, very similar to what you have done with the exception that my source is a sql server and target
is also sql server. in dynamic input is telling me fields name cannot be blank in the formula icon is telling me unknown variable table name. if you can tell what you configured those values to in your mssql test, I would highly appreciate