The script for the basic process will be as follows:

In many transactional systems, rows are only allowed to be added to the system. This is true for many bookkeeping systems. If you make a mistake, you are not allowed to edit or delete the row: you need to add a new transaction to correct the error.

In that case, our basic process is actually too complex. It will work perfectly as it is, but we can modify it to remove the Not Exists clause when loading the QVD. In theory, the QVD should never contain records that we have loaded within the date range. However, in the real world, it is always better to leave the check in place—Exists does not impact an optimized load from the QVD.

Other systems allow users to make adjustments directly to the transactional data. If they do, they will usually (although not universally!) have a field that contains the timestamp for when the modification was made.

In this case, our basic script should work perfectly. You just need to modify the extraction query and make sure that you include a where clause on the field that contains the modified date.

It could be possible that the system that you are reading data from may allow transaction rows to be deleted. The problem for us is that we may have one of the deleted rows already stored in our QVD and we will get no indication (because there can be no modified date on a deleted row!) that the row is gone.

In that situation, we will add an Inner Join load of the primary key value from the data source, just after we have concatenated the rows from the QVD to the modified rows, but just before we store the data to QVD. The Inner Join load will remove any rows from the in-memory table that do not exist in the data source. We can then store the table to file and the deleted rows will no longer exist, for example:

// Check for deleted records
Inner Join (Orders)
SQL SELECT OrderID
FROM QVTraining.dbo.OrderHeader
Where OrderDate >= '20140101';

Note that there is a date on this. We are assuming here that previous years' data is stored in separate QVD files, so we would not be modifying this.

Handling situations when there is no modify date present is tricky and you will need to utilize the assistance of the local DBA or application developer. Often the system will keep a log of changes and you may be able to query this log to obtain a list of the primary keys for the records that have changed in the period since the last extraction.

If there is no such set of records, you may be able to get the DBA or developer to create a database trigger that creates a separate record in a table when a row is inserted or modified. You can then query this table to obtain your list of primary keys.

Whatever the situation, there is often some kind of solution available.

To completely replace a whole table, we put the Replace keyword before the load statement for that table, for example:

Orders:
Replace
Load * 
From [..QVDOrders.qvd] (QVD);

In this case, we assume that the QVD has already been updated (perhaps using the incremental load process) and we need to replace the whole table.

We can also add new rows to a table without having to remove the table. By placing the Add keyword before the load statement, we can leave what we have already loaded and then just add new rows. This can be an effective method of running incremental loads:

Orders:
Add
LOAD OrderID,
    OrderDate,
    CustomerID,
    EmployeeID,
    Freight;
SQL SELECT *
FROM QVTraining.dbo.OrderHeader
Where OrderDate>'$(vLastReloadTime)';
// Update the last reload time
Let vLastReloadTime=Timestamp(Now(), 'YYYYMMDD HH:mm:ss'),

In the last example, the final step was to update a variable. We may notice, however, that there was no option to say whether this should happen if the load is a partial load or a normal reload. All such assignments will happen either way. We can, however, manage this process by using the IsPartialReload function, which returns true or false, depending on the reload type:

If IsPartialReload() Then
  // Do partial reload stuff
Else
  // Do normal reload stuff
End if