create 1000 tables using script

[SL23]

I want to create 1000 tables using one script. Sam set of tables but the table name and the pk should be incremented, Is there a way to do this in teradata...

CREATE SET TABLE s.at1
(
student_number INTEGER NOT NULL,
f_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
lt_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
dpt VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
stdnt_ln_amount DECIMAL(10,2),
expd_grad DATE FORMAT 'yyyy-mm-dd' NOT NULL)
PRIMARY INDEX PK_student1 ( student_number );



REATE SET TABLE s.at2
(
student_number INTEGER NOT NULL,
f_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
lt_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
dpt VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
stdnt_ln_amount DECIMAL(10,2),
expd_grad DATE FORMAT 'yyyy-mm-dd' NOT NULL)
PRIMARY INDEX PK_student2 ( student_number );


.....

 

[jgrogan]

Hi SL23

I am curious why you would need 1000 copies of the same table, but...

If you have recursive queries available (V2R6.2 works, YMMV with earlier releases):

WITH RECURSIVE temp (num) AS (
SELECT 1
FROM   dummy
UNION ALL
SELECT num+1
FROM temp
WHERE num < 1001
)
SELECT 
'CREATE SET TABLE s.AT'||TRIM(num)||      
'    (
      student_number INTEGER NOT NULL,
      f_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      lt_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      dpt VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      stdnt_ln_amount DECIMAL(10,2),
      expd_grad DATE FORMAT ''yyyy-mm-dd'' NOT NULL)
PRIMARY INDEX PK_student'||TRIM(num)||' ( student_number );'
FROM temp;

Notes:
Table 'dummy' contains a single row - does not matter what is in it.
''yyyy-mm-dd'' is yyyy-mm-dd surrounded by pairs of single quotes

If you do not have recursion available to you:
populate table 'dummy' with 1000 rows containing 1000 INTs (column myint used in example below)...

SELECT 
'CREATE SET TABLE s.AT'||TRIM(myint)||      
'    (
      student_number INTEGER NOT NULL,
      f_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      lt_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      dpt VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      stdnt_ln_amount DECIMAL(10,2),
      expd_grad DATE FORMAT ''yyyy-mm-dd'' NOT NULL)
PRIMARY INDEX PK_student'||TRIM(myint)||' ( student_number );'
FROM dummy
order by 1;

Hope this helps

BRgds
JG

 

[jgrogan]

SL23

Oops - I only tried recursion with 20 entries - it has a depth limit of 127. Sorry.

Please use the other (non-recursion) version. Also, it is not necessary to generate a dummy table with 1000 integers; just reference a table with over 1000 rows in it. Use row_number() during generation of SQL, and qualify the ROW_NUMBER returned as being < 1001 for that table like this:

SELECT 
'CREATE SET TABLE s.AT'||TRIM(ROW_NUMBER () OVER (ORDER BY anycolumn))||      
'    (
      student_number INTEGER NOT NULL,
      f_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      lt_name VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      dpt VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      stdnt_ln_amount DECIMAL(10,2),
      expd_grad DATE FORMAT ''yyyy-mm-dd'' NOT NULL)
PRIMARY INDEX PK_student'||TRIM(ROW_NUMBER () OVER (ORDER BY anycolumn))||' ( student_number );'
FROM  #db.#table_with_1000_rows_plus
QUALIFY ROW_NUMBER () OVER (ORDER BY anycolumn) < 1001
;

My apologies - always learning...

BRgds
JG

 

[SL23]

Thanks a lot, sorry I was so busy and did not get a chance to thank you!

thanks I tried with some modification in the sql and it worked...

thanks a lot!

 

[al1024]

When you need a table containing range of integers, you can use SYS_CALENDAR.CALENDAR.DAY_OF_CALENDAR...

SELECT cal.day_of_calendar AS myvalue
FROM sys_calendar.calendar AS cal
WHERE myvalue BETWEEN 1 AND 1000