Incremental SQL Loads

It’s often not practical to process a whole table and all records each time for performance reasons. Instead, a Job may be designed to only process records that have been added or modified since the previous time the Job ran.

There are several ways to do this, and the exact approach depends on the nature and structure of the data and purpose of the task.

If for example we want to copy new rows from one table to another table, this could possibly be done through a single SQL_STATEMENT step, like so:

INSERT INTO my_new_table (activity_id,activity) FROM ng_activity_tb
WHERE activity_id > (SELECT MAX(activity_id) FROM my_new_table)

But in many cases we need to remember the id or timestamp of the last row we copied. This can be done the following way:

Configure two Job parameters:
- Name: fromId, Type: Integer, Default: 0
- Name: untilId, Type: Integer, Default: 0
Create an initial step: Name: initialise, Type: SQL_STATEMENT, ActionOnError: CONTINUE:

SQL: SELECT @fromId := @untilId + 1

Create a second SQL_TO_LIST step:

SELECT @maxValue:=activity_id,a.* FROM ng_activity_tb a WHERE activity_id BETWEEN @minValue AND @MinValue + 10000 ORDER BY activity_id
This copies up to 10,000 rows each time.

Other options include:

SELECT @minValue := @maxValue + 1, @maxValue;

SELECT @maxValue:=activity_id,a.* FROM ng_activity_tb a
WHERE activity_id BETWEEN @minValue AND @MinValue + 100 ORDER BY activity_id;

SELECT @minvalue:=@maxValue,@maxValue:=CASE WHEN MAX(activity_id)>@maxValue+100 THEN @MaxValue+100 ELSE MAX(activity_id) END
FROM ng_activity_tb;

SELECT @minvalue:=@maxValue,@maxValue:=IFNULL(MAX(activity_id),@minValue)
FROM ng_activity_tb WHERE activity_id BETWEEN @minValue AND @maxValue + 100;