INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

create 1000 tables using script

create 1000 tables using script

(OP)
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 );


.....

RE: create 1000 tables using script

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):

CODE

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)...

CODE

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

RE: create 1000 tables using script

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:

CODE

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

RE: create 1000 tables using script

(OP)
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!
 

RE: create 1000 tables using script

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close