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

Background: SQL-Hub: Database Merging Tool

screendecoration

Introduction

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.

screendecoration

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.

MS-SQL:

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

MySQL:

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

SQL-Hub uses methods 1 and 3

All content © SQL-Hub / Burr IST 2018
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