SQL Query: Generate Delta Table

Generates lines for Delta table MyTable_Delta by comnparing MyTable to MyTable_Previous.

MyTable has six columns: a unique id and five data columns (col1 – col5).

See post SQL 2005 MA: Export and Delta Import.

/* Adds */
INSERT INTO dbo.MyTable_Delta
SELECT a.*, 'Add' AS ChangeType
FROM dbo.MyTable a LEFT OUTER JOIN
dbo.MyTable_Previous b  ON a.id = b.id
WHERE b.id IS NULL

/* Deletes */
INSERT INTO dbo.MyTable_Delta
SELECT b.*, 'Delete' AS ChangeType
FROM dbo.MyTable a RIGHT OUTER JOIN
dbo.MyTable_Previous b  ON a.id = b.id
WHERE a.id IS NULL

/* Modifies */
INSERT INTO dbo.MyTable_Delta
SELECT a.*, 'Modify' AS ChangeType
FROM dbo.MyTable a INNER JOIN
dbo.MyTable_Previous b ON a.id = b.id
WHERE a.col1 <> b.col1
OR a.col2 <> b.col2
OR a.col3 <> b.col3
OR a.col4 <> b.col4
OR a.col5 <> b.col5