asked on

Execute SQL Agent Job in a loop to process for historical load

Hi Experts,

I want to execute the SQL Agent Job (list of stored procedures) in a loop to process in a batch for historical loads.

For example , I want to do historical load for one year , I want to implement this logic in SQL Agent Job in small batches of 4 days for 92 iterations (365/4)

Note : Each Iteration , I can load only 4 days of data , as the data size is massive (~130 Million records for 4 days). So I want to process historical loads in small chunks of 4 days and batches.

In this example below , how to execute this logic in loop , auto increment the values of @historical_begin_date && @historical_end_date for each iteration and execute the stored procedures in a loop.

Example
Declare @historical_begin_date date time
Declare @historical_end_date date time

while @historical_begin_date <@historical_end_date
begin
Exec Stored procedure 1
Exec Stored procedure 2
end

Please walk me through the solution with code .

Thanks,
Sreekanth.

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

>auto increment the values of @historical_begin_date && @historical_end_date for each iteration
Wouldn't be a bad idea to create a new table just to store the most recent historical_end_date , say...

CREATE TABLE tLog (
   id int identity(1,1), 
   historical_end_date date, 
   run_dt datetime DEFAULT GETDATE())

Open in new window

Then modify your T-SQL to

Get the most recent historical_end_date

Calculate @historical_begin_date and @historical_end_date based off of that

When successful write a row to the above tLog table, so the next time it runs it can key off of that date.

n_srikanth4

ASKER

Good