Types of SQL updates

Normal Incremental

The Normal incremental update type leaves existing data intact and loads new content, records from a certain value of the "incremental" field.

Configuration of a Normal incremental

1. Incremental field: is the column of a table or the alias of a field present in the SQL query (and therefore the "DB alias" or Fieldname of the field in the dataset) to be used in the incremental condition, usually an auto-incremental numeric column or a date.
2. Variable: is the variable used internally by Vision that takes the maximum value of the incremental field in the dataset before the update ($last_value$). This value is not modified, unless a more complex incremental condition needs to be implemented, in which case the Vision administrator must be contacted to evaluate the case.
3. The incremental condition is added to the SQL statement by inserting in the where part of the query a special text: $condition$. Vision understands this text as a variable, when executing the query, it first resolves the value of $last_value$ and replaces $condition$ by campo_incremental > last_value . With this in mind, the user has to write a valid SQL query, inserting the $condition$ variable correctly in the where clause of the query.

Valid SQL syntax using an alias in the incremental condition

Incremental Window

The incremental update type Window is an extension of the normal incremental, as it inserts data starting from a value prior to the maximum value of the incremental field in the dataset. This update is only allowed using an incremental field of type date: the "window" is the interval that the user indicates by configuration. All dataset data within the window are deleted, and the SQL query is launched with a condition that results in data starting from the initial date of the window, which includes records with previously loaded dates and with new dates not loaded (like the normal incremental). Records in the dataset prior to the start of the window are left untouched.

This type of update provides an answer to the use case in which, without wanting to make a complete update, it is necessary to load new records and also modify some recent records, without going into the detail of which ones they are. Situations like this occur frequently in practice: accounting data in an open fiscal year that are corrected, sales amounts that are modified due to discounts applied, etc.

The configuration of this type of update is divided into 2 parts:

1. The incremental part proceeds in a similar way to the normal incremental: you need to indicate the incremental field and insert in the where part of the SQL query the text $condition$, which will be understood as a variable and Vision will replace it with the expression campo_incremental >= window_start_value.
2. It is indicated how to determine the starting date of the window, which will be a date prior to the current date window_start_value = today - X "time units". There are 3 different options to determine X and time units:
   • Current period: the window is a dynamic period covering the "current" time interval corresponding to the selected unit, which can take the values years, months, weeks, days, hours or minutes.For example, if the unit "years" is chosen the start of the interval begins on January 1 of the current year, if "month" is chosen on the 1st of the current month, etc.
   • Exact interval: the window is a fixed time interval as indicated by the number of periods and the time unit, e.g. if "2 weeks" is specified and the update is executed on a "Thursday" the window starts on Thursday 2 weeks ago.
   • Full interval: this option is a combination of the current period and the exact interval, as it "rounds" the start of the window to the full interval of the selected unit, e.g. if "1 year" is specified the window starts on January 1st of the year before the current one (it goes one year into the past and rounds according to the time unit), if "0 months" is specified the interval starts on the 1st of the current month, if "2 weeks" is specified the interval starts on Sunday of 2 weeks ago (the week in Vision starts on Sunday and ends on Saturday).

Configuration of an incremental Window

Incremental Last Changes

The Incremental Last Changes incremental update type leaves a part of the present data intact, updates (or deletes) another part of it, and also loads new content. The configuration of this type of update is divided into 3 parts:

1. Incremental field: it is the column of a table or the alias of a field present in the SQL query (and therefore the "DB alias or Fieldname" of the field in the dataset) to be used in the incremental condition, usually the creation/update date of each record.
2. A primary key (PK) of the record needs to be defined. In the corresponding text box, the names of the fields that form this PK are written (press Enter to enable each written field). In the update process, for each record that arrives as a result of the query, the equivalent record in the dataset is searched by comparing by the PK: if it is found it is updated by the new one, and if it is not found it is inserted as it is since it is a "new" record.
3. The incremental condition is added to the SQL statement by inserting in the where part of the query a special text: $condition$. Vision understands this text as a variable, when executing the query, it first resolves the value of $last_value$ and replaces $condition$ by campo_incremental > last_value .With this in mind, the user has to write a valid SQL query, inserting the $condition$ variable correctly in the where clause of the query.
4. The process allows not only to update data but also to delete records. To do this, a field must be included in the SQL query that must be called "_delete": when the value of the field is "true" and the PK of the record matches the PK of a row in the dataset, it is deleted. However, this part is not mandatory: if it is not included, no record is deleted.

Configuration of an incremental Last changes