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;
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.
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.
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.
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.
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.
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!
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?
This is a question regarding the post http://forums.teradata.com/forum/general/tuning-query. CALL_DETAILS is a partitioned table.
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?