Is there a method to combine multiple monthly data tables into one master table? For example, I have ABCD_201201, ABCD_201202, ABCD_201203, etc. tables and I want to combine those data into one table. How can I create a variable for YYYYMM so that I don't need to hard code that value every month? Because there will be a new table created every month...
I think you can create a parametrized macro to create a table for every month. just pass the current month in the parameter and execute the macro.
To combine data from all of these tables, you can use union all for all these tables e.g.
SELECT * FROM ABCD_201201
SELECT * FROM ABCD_201202
SELECT * FROM ABCD_201203
what is the reason for creating one table per month?
Can't you have one table and partition it by a date colume with RANGE_N each month?
Yes, I'm doing this manually right now.
But I'd like to learn how to create a macro that can help me input the month name (e.g. 201201) automatically.
Unfortunately, a single history table is not available for this data. So I have to work around it in order to capture this data every month. Any suggestions are greatly appreciated.