Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

i-catching solutions
Browse by Tags · View All
SQL Server 26
Script toolbox 16
T-SQL 12
SQL 12
Geniiius 8
SQL Server 2012 7
Performance optimization 6
DMV 5
debugging 5
Debugging 5

Archive · View All
November 2011 6
May 2012 4
April 2012 4
January 2012 4
February 2012 3
March 2012 3
October 2011 2
December 2011 2
September 2012 1
June 2012 1

Geniiius's Blog

MERGE with OUTPUT clause

Feb 21 2012 12:00AM by Geniiius   

A few days back I was presented with a theoretical challenge, and now I thought I would share my solution with you. The challange was to keep track of changes on a table that we had only a readonly access to, and keep a history of the changes. Furthermore the table didn’t have a column to track the last modified date, but a primary key was available though.
We didn’t need to track the changes realtime, but once or twice a day was preferable. I came up with a solution using the MERGE stament, and applying the OUTPUT clause. Let me show a simple demo setup:

CREATE TABLE ReadOnlyTable (
    Id INT PRIMARY KEY,
    Col1 INT,
    Col2 INT,
    Timestamp DATETIME
)

CREATE TABLE HistoryTable (
    Id INT PRIMARY KEY,
    Col1 INT,
    Col2 INT,
    Timestamp DATETIME
)
GO

INSERT INTO ReadOnlyTable (Id, Col1, Col2, Timestamp)
VALUES
    (1, 1, 1, GETDATE()),
    (2, 2, 2, GETDATE()),
    (3, 3, 3, GETDATE())
GO

 

The ReadOnlyTable represents the table from a third party system, that we only have read access to.
The HistoryTable is a table to hold a copy of the snapshot at the scheduled compare times (once or twice a day).

I came up with this MERGE statement, which compares the entire content of the tables for changes – so it is not lightweight in any way. But luckily that wasn’t an issue Smiley The statement looks like this:

MERGE INTO HistoryTable Dst
USING ReadOnlyTable Src ON Dst.id = Src.id
WHEN MATCHED AND (Dst.col1 <> Src.col1 OR Dst.col2 <> Src.col2 OR Dst.Timestamp <> Src.Timestamp) THEN
    UPDATE
    SET Dst.col1 = Src.col1, Dst.col2 = Src.col2, Dst.Timestamp = src.Timestamp
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, col1, col2, Timestamp)
    VALUES (Src.id, Src.col1, Src.col2, Src.Timestamp)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    GETDATE() AS ChangeDate,
    COALESCE(Inserted.Id, Deleted.Id) AS Id,
    CASE
        WHEN Deleted.Id IS NULL AND Inserted.Id IS NOT NULL THEN 'i'
        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NOT NULL THEN 'u'
        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NULL THEN 'd'
    END AS ChangeType,
    deleted.col1 AS col1_before,
    deleted.col2 AS col2_before,
    deleted.Timestamp AS Timestamp_before,
    inserted.col1 AS col1_after,
    inserted.col2 AS col2_after,
    inserted.Timestamp AS Timestamp_after;

The basic MERGE statement just joins the source and destination on the Id column. If the Id exists in the destination table (HistoryTable) and if at least one of the other columns have changed, then we update. Rows that do not exist will be inserted. Deleted rows will simply be removed from the HistoryTable.

Given the three rows in ReadOnlyTable and no rows in HistoryTable, the execution of the MERGE statement will return this:

image

Because the rows are all new, there are no content in the *_before columns. To illustrate changes performed in the third party database, I will now update a few rows, and run the MERGE statement again:

 

UPDATE ReadOnlyTable SET Col1 = 42
WHERE Id = 1
GO
DELETE FROM ReadOnlyTable
WHERE Id = 2
GO
INSERT INTO ReadOnlyTable (Id, Col1, Col2, Timestamp)
VALUES (4, 4, 4, GETDATE())
GO

 

The MERGE now returns this:

image

 

Now we have a way to asynchronously track changes performed in the readonly table. All we need now, is to create a table to store the output data, and add the INTO <table name>. The final table and MERGE statement looks like this:

 

CREATE TABLE ReadOnlyTableChanges
(
    ChangeId INT IDENTITY PRIMARY KEY,
    ChangeDate DATETIME,
    Id INT,
    ChangeType CHAR(1),
    Col1_before INT,
    Col2_before INT,
    Timestamp_before DATETIME,
    Col1_after INT,
    Col2_after INT,
    Timestamp_after DATETIME
)
GO

MERGE INTO HistoryTable Dst
USING ReadOnlyTable Src ON Dst.id = Src.id
WHEN MATCHED AND (Dst.col1 <> Src.col1 OR Dst.col2 <> Src.col2 OR Dst.Timestamp <> Src.Timestamp) THEN
    UPDATE
    SET Dst.col1 = Src.col1, Dst.col2 = Src.col2, Dst.Timestamp = src.Timestamp
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, col1, col2, Timestamp)
    VALUES (Src.id, Src.col1, Src.col2, Src.Timestamp)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    GETDATE() AS ChangeDate,
    COALESCE(Inserted.Id, Deleted.Id) AS Id,
    CASE
        WHEN Deleted.Id IS NULL AND Inserted.Id IS NOT NULL THEN 'i'
        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NOT NULL THEN 'u'
        WHEN Deleted.Id IS NOT NULL AND Inserted.Id IS NULL THEN 'd'
    END AS ChangeType,
    deleted.col1 AS col1_before,
    deleted.col2 AS col2_before,
    deleted.Timestamp AS Timestamp_before,
    inserted.col1 AS col1_after,
    inserted.col2 AS col2_after,
    inserted.Timestamp AS Timestamp_after
INTO ReadOnlyTableChanges; --This is added

 

Simple as that Smiley


Republished from geniiius.com [45 clicks].  Read the original version here [3 clicks].

Geniiius
135 · 1% · 369
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"MERGE with OUTPUT clause" rated 5 out of 5 by 2 readers
MERGE with OUTPUT clause , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]