How can we loop through and extract multiple tables and load into a different database?

[Stevemcdonald]

Hi,

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.

Table1

Table2

Table3

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.

e.g. 

Target Database Corresponding Table Names

SRC_Table1_EBS

SRC_Table2_EBS

SRC_Table3_EBS

Many thanks in advance for any answers and guidance.

Steve

[LordNeilLord · Accepted solution]

Hey @Stevemcdonald

You can use a batch macro to achieve this....

Inside the macro you use the dynamic input to download the table, then create the new table name and finally write back out to your new database

Outside of the macro, you connect up your list of tables...add a recordID and then use this to control the batch (so it does one at a time)

I've mocked you up an example using Access but I tested it using SQL sever and it works just fine :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[jrgo]

Hi @Stevemcdonald,

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.

Hope this helps!

Jimmy

[Stevemcdonald]

Thank you very much!

@LordNeilLord - this works well, I have just tried executing it locally.

I have a of questions if you don't mind?

When reading in from a specific schema, e.g. HR - where would you define this?

Thanks again

Steve

[Stevemcdonald]

jrgo

Thank you - sounds like a great idea.

I'm sorry but do you have an example of this? Let's assume the schema is called SYSADM?

Many thanks

Steve

[LordNeilLord]

If all the tables are in the same schema
.. You just need to define that once in the dynamic input tool (it will use the connection you have used as the template)

Neil

[LordNeilLord]

When @jrgo said schema, he meant the table schema (like column a, column b) not the dB schema (like sysdba)

The dynamic input tool can download multiple tables and union them together but as (presumably) the tables have a different layout, you'll need a batch approach (to do one at a time)

Neil

[jrgo]

@Stevemcdonald,

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.

Best,

Jimmy 

[Stevemcdonald]

Hi both (@LordNeilLord, @jrgo) 

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.

Thanks again

Steve

[eduardocaceres]

Hi,

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