Connecting data with multiple tables in a database

Hi,

I have like a 100 tables in a database and all of these tables contain one column which is common to all. I want all the distinct values from this common field of 100 tables in a sheet.

Since there are 100 , i don't want to reference each of the tables individually in a query or by the use of 100 different connection strings !

Anyway out to reduce the effort ?

Thanks,

Rajat

Developer Tools

Tips and Tricks

Optimization

Database Connection

Accepted answers

MichalM

@rajatjain1

You will be able to achieve this using the Dynamic Input tool. You will feed in the list of 100 tables you'd like to query, dynamically update the reference to the table in your SQL one by one and get the output nicely unioned into one big table in the end.

We've covered how the tool can be used in our Tool Mastery series.

All comments

MichalM

@rajatjain1

We've covered how the tool can be used in our Tool Mastery series.

JoeS

Hi @rajatjain1 as @MichalM has mentioned you'll want to use the Dynamic Input tool to do this for you.

You can also combine it with a quick Select statement to return the list of tables as well:

Select * From Alteryx.sys.tables

This will return all the tables from he "Alteryx" database, so please update this to be the one in question.

It should give you a helping hand in creating the list of 100 tables as well.

I have then built a quick select statement after:

"Select [MyColumn] from [" + [name] + "]"

Please update "MyColumn" to be the column you want from all the tables.

Then lastly you'll need to update the Dynamic Input tool to point to your SQL Database.

ReturnTableList.yxmd

rafalolbert

Hi @rajatjain1,

Dynamic Input tool will be your best friend here, see my example below:

1) i've created 2x tables in Teradata to do this (2nd column is different between them when it comes to field type and values present):

2) I've listed all tables in a column and created a macro to query them one by one - this is not ideal as connection needs to be opened and closed each time, which is an expensive procedure (time-wise), but it gives you the isolation, allowing you to address the setup of every table dynamically (different schema, column names, types etc.) - make sure to select one of the auto configurations for your macro (Ctrl + Alt + D) -> Properties, as shown below:

Inside the macro i've appended the source, to allow you to do the joining and manipulation after all data is brought into the main workflow -the approach here collapses all headers into 1 cell and all records into 1 cell: delimited by newline for each record and comma to designate individual fields:

3) the final result looks like this and involves dis-assembling the collapsed records and headers:

Please know that you'll have to connect to your own database to make it to work (i had to break the connection, so when you load the macro it will prompt some errors).

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

macro.yxmc

workflow.yxmd

workflow.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703