What is the easiest way to do a mass export of 300 tables in a schema with an in list. The in list is set as a variable in the beginning of the automation script. There are 2 clients with the same table names in each.
Example of export I need for 300 different tables:
SELECT * FROM client1.table1 WHERE LOAN_NUMBER IN (&VARIABLE)
UNION ALL
SELECT * FROM client2.table1 WHERE LOAN_NUMBER IN (&VARIABLE)
Is creating 300 different exports the only way this is possible?
The exports need to go to .txt files that are named the same as the table.
Example:
table1.txt
Thank you for any assistance/guidance. I do not look forward to doing this manually 300 times. The initial build will be a time consuming monotonous bore.
I don't have the bandwidth right now to test my idea, but with TDP's Automation Designer, it's possible to create an Automation job that
- Reads in your table names, probably from a flat file or a table (the "in list", if I'm understanding you correctly) and assign the name to a variable
- Loops through each of the names in the list, and for each one...
a. Execute your query (using the current variable-ized table name)
b. Export to CSV using the variable value for the export file name
When I get some time next week, I can try to set up an example and share if I'm successful (unless you beat me to it!)
Figured it out using variables and looping. We loop through table names in the schema with a query which dynamically names the files on export and moves on to the next in the list of looping.
Glad the idea worked out. Would you mind sharing any details of the automation job? I know there are other users who would like to do the same thing.
