create mutiple tables from an existing table with while loop

Database
Enthusiast

create mutiple tables from an existing table with while loop

This should be a common issue but cannot simply find an exact solution across forum. Thanks for hlep!

Here is what I need:

create table tab1 as (select * from mydata where seq=1) with data;

create table tab2 as (select * from mydata where seq=2) with data;

......

create table tab10 as (select * from mydata where seq=10) with data;

Instead of executing 10 queries above, there must a simple way such as 'while loop' with i=1,2,....10) that generates 10 tables (tab1,2...10). Please advise!

7 REPLIES
Teradata Employee

Re: create mutiple tables from an existing table with while loop

You can use a dynamic SQL like this one:

select 'create table tab'||(day_of_calendar (format '999')(CHAR(3)))||' as (select * from mydata where seq='||day_of_calendar||') with data;' from sys_calendar.calendar where day_of_calendar<=10
Enthusiast

Re: create mutiple tables from an existing table with while loop

Thanks for advice on dynamic SQL! I tested but tab001...was not created (or existed). I have noticed that "... seq=      1 ..." where multiple blanks appear between '=' and 'number' (not character here). Is this a problem? If not working out in case, any other solutions such as while-loop and how? 

Teradata Employee

Re: create mutiple tables from an existing table with while loop

I tried the process before sending and It works

Enthusiast

Re: create mutiple tables from an existing table with while loop

I copied your entire code here:

select 'create table tab'||(day_of_calendar (format '999')(CHAR(3)))||' as (select * from mydata where seq='||day_of_calendar||') with data;' from sys_calendar.calendar where day_of_calendar<=10

Ran code above and I saw ten queries under Answerset and seem right. But running "select * from tab001" ends up with 'tab001 does not exist'. Did I miss something such as call-procedure? Do you have a sample data to send over for testing (3 iterations are fine)? Thanks!

Senior Apprentice

Re: create mutiple tables from an existing table with while loop

Well, the query only returns 10 CREATE TABLE statements which need to be executed.

Cut&Paste them to your query window and run them.

To do that in a loop you need a Stored Procedure, which is the only place where you can create and execute Dynamic SQL without manual intervention.

Enthusiast

Re: create mutiple tables from an existing table with while loop

Hi Dnoeth, I SEE! I missed Cut&Paste&Run. Now, I get them. Thanks a lot! Sorry I am new in Teradata!

But the actual query in each iteration is rather long. So I do like to try a Stored Procedure.

I did quite bit serach about while-loop but still cannot figure out how. Could you please provide code to execute my case (see my first post)? 

Enthusiast

Re: create mutiple tables from an existing table with while loop

Have not got any follow up! As an alternative, if possible, can some one guide me to textbooks or references that can help resolve this issue! Thanks a lot!