All Forums Database
cklam 4 posts Joined 06/13
06 Nov 2013
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...

Raja_KT 1246 posts Joined 07/09
06 Nov 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

cklam 4 posts Joined 06/13
07 Nov 2013

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

M.Saeed Khurram 544 posts Joined 09/12
08 Nov 2013

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

ulrich 816 posts Joined 09/09
08 Nov 2013

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Raja_KT 1246 posts Joined 07/09
08 Nov 2013

Hi Lam,
I suggest you read Teradata material on partition and see how it suits your requirements. It is easy.
Cheers,
Raja
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

cklam 4 posts Joined 06/13
08 Nov 2013

 

 

 

 

 

 
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.
 

You must sign in to leave a comment.