Insert into target table from source table for each date for a specific date range

Database

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;

10 REPLIES
N/A

Re: Insert into target table from source table for each date for a specific date range

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.

N/A

Re: Insert into target table from source table for each date for a specific date range

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.

Re: Insert into target table from source table for each date for a specific date range

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.

N/A

Re: Insert into target table from source table for each date for a specific date range

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.

Re: Insert into target table from source table for each date for a specific date range

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.

Re: Insert into target table from source table for each date for a specific date range

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!

N/A

Re: Insert into target table from source table for each date for a specific date range

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?

Re: Insert into target table from source table for each date for a specific date range

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

N/A

Re: Insert into target table from source table for each date for a specific date range

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?