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.
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.
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).