screen decoration screen decoration
Data at the heart of your organisation
Trial: £free
Standard: from £450

Background: SQL-Hub: Database Merging Tool



Burr IST Ltd have been working with a wide range of customers since 2001 on all sorts of reporting, integration and analysis projects using SQL-Hub, JetViewer and other products.

Over the years we have come across a lot of the same challenges and looked at many ways of solving them

These articles explore some of the things that are often discussed and the pros and cons of a number of different solutions. These discussion are intended to give an understanding of some different types of scenarios and how they can be addressed.


Detecting Changes in Database Data

Often we need to find out which records in a database have changed since a certain point (when we last looked).

There are a number of ways of doing this and the choice of the best solution depends on the circumstances.

1)    The records are timestamped

If the records in a database are timestamped (either with an automatic database timestamp or reliably by an application working with the database) then it's easy to see which records have been changed after a particular point.

2)    Compare new version with the old version

If there is a copy of the old version of a table it is quite straight forward to compare the values field by field to see if a record has changed. However to do this in the case of a remote database all of the data needs to be transfered before the comparison can be made.

3)    Compare new CRC with the old CRC

Rather than comparing the actual values of all the fields a CRC value can be calculated over the record - if the record changes the CRC value changes. If the old CRC value for a record has been stored a comparison with the current CRC will show which records have been modified. The CRC can be calculated at source (e.g. on a remote server) so there is no need to transfer the whole record to check for changes, only the CRC value.


    SELECT OrderID,checksum( OrderID,ProductID,[Value],OrderDate)
    FROM Orders


    SELECT OrderID ,crc32(CONCAT_WS('#',OrderID,ProductID,Value,OrderDate))
    FROM Orders

SQL-Hub has implementations of methods 1 and 3 - but only when used in a custom deployment

Note that if records in the source database can be deleted you will also need a way to detect deletions - typically by comparing a list of Unique IDs either with the target database or with an "old" list of IDs from the source system.

All content © SQL-Hub / Burr IST 2019
Site by Burr IST, design by foliozine

Burr Information Systems Technology Ltd

28 Park Avenue, West Point, Manchester, M19 2EE

Tel: +44 (0) 161 327 2934