Need automated script to create a table with variable name.

[DataAnalyst]

Hi,

1. Can anyone please suggest a way setup automated scripts to create tables (thru BTEQ script/macro or whatever) that have datestamp in their names?

Example:
This week the script should create TableName_20021110.
Next week it should create TableName_20021117 etc.

Both the following are not being accepted:
/** #1 Create using macro **/
Create Macro Test_Macro1_M (tabName Varchar(30))
As (Create Table :tabName (Col1 Decimal(16));

/** #2 Create and rename **/
Create Macro Test_Macro2_M (tabName Varchar(30))
As (Rename Table Old_Name To :tabName;

One option that struck me to accomplish this is to generate the create statement dynamically in a file and run the file.
Example: Export this result to a file and run the file: &quot;Select 'Create table tab'||<datestamp>||'(...);';

But in this method, req#2 below will be very complicated.

2. After creating the table, I need to populate the table and also run a bunch of automated select queries against it.
So in &quot;Select * From TableName_20021110&quot;, after the FROM, the name should automatically be the above created table name.

Too much to ask for?

Thanks for you time.

 

[dnoeth]

Instead of using BTEQ to create a RUN-file use a shellscript and shell parameters:

#!/bin/ksh

exec /your path/bteq <<BTQEND

.logon ...

create table tabname_$1 ....

select * from tabname_$1

.quit

BTEQEND


Dieter

 

[tdatgod]

Hi,

What is the reason you want to add the date stamp? Is this simply to make the table name UNIUE? How long does this table persist? I mean when does it get deleted/Dropped?

What if 2 people run the same script at the exactly the same time won't they both get the same table_datetimestamp created and one of them will blow up with table already exists or worse they will both try to populate the same table at the same time.

It sounds like you want a Volatile or Global Temporary table.

The DBA creates these table definitiona once and then when a users says

insert into mytemptable .....

it is materialized with a well known name but only that user can access it for as long as they remain logged on ( or until the next reset in the case of volatile tables )

They automatically get deleted/dropped when the user logs off so there is no maintenece headache about remember to go back and delete them for the user if the script fails half way through and doesn't do the delete.

 

[rrrkrishnan]

Hi Dieter

How do I do this in a DOS batch script, for we are using Teradata on Windows NT. Please let me know.

Thanks
Krishnan