All Forums Database
neelabi_24 1 post Joined 01/15
21 Jan 2015
Dynamic Table Name

Hi,
I have set up a BTEQ of an SQL code through PUTTY.
The code runs weekly. However, I run a drop statement and then a create table statement, thus losing the table of the previous week. Is there anyway for me to create a table of the following format so that I retain all the tables that I have created ?
CREATE TABLE TABLE_NAME_<CURRENT_DATE> AS
(SEL * FROM XYZ)
WITH DATA;
 
Please let me know at the earliest! Thanks in advance.
Warm regards.

Raja_KT 1246 posts Joined 07/09
21 Jan 2015

You can put your bteq script in a unix script and pass unix date to your create table or you can pass it as parameter from your unix script.
Another choice, to avoid this , you may have SVN, VSS, PVCS or SCM tools  where you can checkin/out.

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.

pathanjali 4 posts Joined 12/14
01 Jun 2016
CURR_DT=`date +%Y%m%d`
bteq << td_bteq_content
 .logmech ldap
    .logon ${SERVER}/${USER},${PASSWORD}
    .SET TITLEDASHES OFF
 
select 1 from dbc.TablesV where databasename = database and TabkeName = 'table_$CURR_DT';
 
.IF ACTIVITYCOUNT = 0 THEN GOTO OK
 
DROP TABLE TABLE_$CURR_DT;
 
.LABEL OK
 
CREATE TABLE TABLE_$CURR_DT AS (SEL * FROM TABLEXXX) WITH DATA;

    .exit
td_bteq_content

you can write a unix shell script to pass the parameter to the table name. Below code can help you.
 

 

 
 

--Pathanjali
"Great minds discuss ideas; average minds discuss events; small minds discuss people"

You must sign in to leave a comment.