Dynamic Table Name

Database

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.

4 REPLIES
Enthusiast

Re: Dynamic Table Name

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.

Re: Dynamic Table Name

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.

New Member

Re: Dynamic Table Name

Hi pathanjali,

 

I have a question with yout script. If the CREATE TABLE is in a file (e.g. CreateDynamicTable.sql) how can I to call the file and it take the variable $CURR_DT? That because I tried execute a file with .RUN FILE and it does'nt take the variable and it interprates as a String

 

Thanks.

Teradata Employee

Re: Dynamic Table Name

$CURR_DT would have to be exposed to the shell to get evaluated.  In other words, somehow CreateDynamicTable.sql needs to be transformed into a shell script that runs bteq and includes the contents of the SQL.  You could manually edit the .sql file and create a CreateDynamicTable.sh file, or you could write another general-purpose shell script that would dynamically create such a file and execute it.   The .sh file would look something like:

 

#!/bin/bash

bteq <<EOF >something.out

.logon .../...,...

<------ place contents of CreateDynamicTable.sql here --------->

.quit

EOF