How to make SQL Delta tables

As I covered in the SQL efficiency post, I snapshot my SQL views to a table before importing. So while I’m doing that I may as well compare it to the last snapshot, and get myself a nice, quick to import, delta table.

Let’s say I have a view called PeopleData. I also need the following three tables:

• PeopleData_Snapshot (the latest copy of the view)
• PeopleData_Archive (the previous snapshot)
• PeopleData_Delta (the difference between the Snapshot and the Archive)

The Snapshot and Archive tables have an identical design; the Delta table needs an extra “ChangeType” column, which will contain either “Add”, “Delete” or “Modify”. (There’s an extra possibility for Multivalue tables, but we’ll go into that another time.)

As an aside, there’s a quick way to replicate SQL tables. One you have one table set the way you want you can right-click it and choose All Tasks -> Generate SQL Script -> Preview. Copy and Paste the displayed script into a Query Analyzer window; change the Table name and run the script. Hey presto, instant table!

The way I generate a Delta table is to look at the Adds, Deletes and Modifies separately.

Add

We want to find all the records that are in the Snapshot table, but not in the Archive table. We do this using the trick of a left outer join. Here the table on the left is listed in full, and if a record can’t be joined with the table on the right we get a NULL value. The objectid column here contains our unique identifying filed – perhaps a staff number or a uid.

INSERT INTO People_Delta
SELECT s.*, ‘Add’ AS ChangeType
FROM People_Snapshot s LEFT OUTER JOIN
People_Archive a ON s.objectid = a.objectid
WHERE a.objectid IS NULL

Delete

Finding the Deletes is just a matter of lining the tables up the other way around:

INSERT INTO People_Delta
SELECT a.*, ‘Delete’ AS ChangeType
FROM People_Archive a LEFT OUTER JOIN
People_Snapshot s ON a.objectid = s.objectid
WHERE s.objectid IS NULL

Modify

The Modifies are a bit more involved. We do an inner join now, because we are only interested in records that are in both tables. We then compare each record field by field to pick up any changes. Luckily SQL is very good at this sort of thing!

INSERT INTO People_Delta
SELECT a.*, ‘Modify’ AS ChangeType
FROM People_Archive a INNER JOIN
People_Snapshot s ON a.objectid = s.objectid
WHERE a.field1 <> s.field1
OR a.field2 <> s.field2
OR a.field3 <> s.field3

You should now have a table that is suitable for your SQL MA and can be specified on the Connect Database MA Properties page.

Other relevant posts:

Speeding up Import times from SQL MAs
Attribute-Level Imports
Delta and Multivalued Combined
A Cheater’s Guide to DTS Packages
Some Thoughts on Clearing Delta Tables
Combining DTS with MIIS Imports