All Forums Database
td_user001 16 posts Joined 06/15
15 Jul 2015
Insert into target table from source table for each date for a specific date range

Can we loop thru insert into a target table using SQL?

for each date_col in DATE '01/03/2014' to DATE '31/05/2015' loop
insert into target_tbl
 select col1, col2,col3 from source_tbl where ins_date = date_col;
end loop;

dnoeth 4628 posts Joined 11/04
15 Jul 2015

This translates to a basic

insert into target_tbl
 select col1, col2,col3 from source_tbl 
 where ins_date BETWEEN DATE '2013-03-01' and DATE '2015-15-31'

Otherwise you need a Stored Procedure to run a loop.
Or simply cross join to a calendar table or use EXPAND ON.

Dieter

TD_Raj 50 posts Joined 05/10
15 Jul 2015

exactly what i thought.
Why you need loop here when you can use between ?
If the requirement is such, then you have to write stored proc to accept start & end data and then you can loop.

td_user001 16 posts Joined 06/15
15 Jul 2015

The table source_tbl  is a partition table for each day and hence, assumpption is that it would be faster to hit single partition and then insert for each day.

dnoeth 4628 posts Joined 11/04
15 Jul 2015

I best case target is partitioned the same as source, otherwise there will be 821 full table scans of the target caused by the loop vs. 1 for a single statement.

Dieter

td_user001 16 posts Joined 06/15
15 Jul 2015

Thanks for your reply. I did not get your point.
Issue is that we have to pick the data from this source table ( this is a partitioned table) for around 5 months date range.The tableis partitioned by each day and holds data for 6 months.

Not sure how to tune the query for such a big timeframe.

td_user001 16 posts Joined 06/15
15 Jul 2015

I want to loop thru each day partition for the given timeframe and my understanding is that the sql query would be more optimal as it would hit the partition only each time.
To hit the sql with such a 5 months timeframe might result in the query to run for a long time.Please correct me if I am wrong here. Thanks!

dnoeth 4628 posts Joined 11/04
15 Jul 2015

I calculated the number of days wrong, but it's totally independant of that number.
If you run a single statement it also hits each partition only once.
And it's definitely faster to run 1 query for 5 months than 180 daily queries.
Of course if you run out of spool (or your Transient Journal gets too large) you might consider smaller ranges, maybe one per month, but try to avoid one per day.
What's the PI and partitioning of both tables?

Dieter

td_user001 16 posts Joined 06/15
15 Jul 2015

This is a question regarding the post http://forums.teradata.com/forum/general/tuning-query. CALL_DETAILS is a partitioned table.

zskuza 15 posts Joined 04/11
25 Nov 2015

I have a similar problem - call data that is partitioned by day, that I want to aggregate into a new physical table with different partitioning. Unfortunately my environment has Teradata Workload Management limits so if I extend my query to work across all partitions the final step of merging data into the physical table is estimated at many hours and Workload Manager aborts the query.
Can anyone suggest an approach that might work, other than for me to loop and process one day at a time?

zskuza 15 posts Joined 04/11
26 Nov 2015

I solved this by performing a sub-select to aggregate data, grouping by partition fields, then the sub-select was joined to other tables for filtering, etc.
 
You have to love a DBMS that can churning a few billion records down to a manageable volatile table size in < 5 minutes.
Teradata is SO GOOD! :-)

You must sign in to leave a comment.