×
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!
  • Students Click Here

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

Students Click Here

Jobs

create 1000 tables using script

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!

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