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

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

TableList.xlsx

Input

Output

Accepted answers

LordNeilLord

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

Data Lover

SQLBatch.yxzp

SuperStore.7z

All comments

LordNeilLord

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

Data Lover

SQLBatch.yxzp

SuperStore.7z

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

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

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

Doug_Accountant

Thanks for the info! I actually just built one of these to convert all tables and existing queries to yxdb format. If you want to get all the table and query names from an accdb file quickly you can use the following SQL in a new query:

SELECT MSysObjects.Name AS table_name

FROM MSysObjects

WHERE LvProp IS NOT NULL AND Name <> "MSysDb" AND Name <>"SummaryInfo";

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335