Dynamic Table Names

Database
Fan

Dynamic Table Names

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

6 REPLIES
Enthusiast

Re: Dynamic Table Names

Hi,

Run in a bteq with your script in a run file. Get the date from any where like dictionary table concatenate with your creation script . 

use run file. Hope this clue is enough.

like select 'create table tab_;||(CAST YOUR GIVEN DATE AS YYYYMMDD).........;'(TITLE '')

.RUN FILE .....

Cheers,

Raja

Fan

Re: Dynamic Table Names

Sorry, but I'm not familiar with bteq. Can I do this in SQL?

Enthusiast

Re: Dynamic Table Names

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
UNION ALL
SELECT * FROM ABCD_201202
UNION ALL
SELECT * FROM ABCD_201203
Khurram
Supporter

Re: Dynamic Table Names

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?

Enthusiast

Re: Dynamic Table Names

Hi Lam,

I suggest you read Teradata material on partition and see how it suits your requirements. It is easy.

Cheers,

Raja

Fan

Re: Dynamic Table Names

 

 

 

 

 

Yes, I'm doing this manually right now. 

SELECT * FROM ABCD_201201

UNION ALL

SELECT * FROM ABCD_201202

UNION ALL

SELECT * FROM ABCD_201203

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.