CTEs, MERGE, and a couple of broken rules

CTEs, MERGE, and a couple of broken rules

I\’ve thought about this next post for quite some time. Not the content, but whether I should post it up on the internet or not.

You see, I\’ve got a few rules about blogging, and if you\’re reading this, then I\’ve just broken a couple of them.

Rule 1 was \”Always remove any details that could be used to identify a specific system, person or organisation\”. I\’ve broken this a little, because I\’ve not altered the scripts herein one iota, but (if you\’ve read other articles in this blog) you already know I dabble in Sharepoint a little. And, in my defence, the scripts are specific to a setup for a solution that may not eventually exist, but I think they do illustrate the point quite well (feel free to contradict me on that).

Rule 2 was \”never post the same content internally and externally\”. This one is well and truly shattered, since I\’m now reproducing the content of a post I did internally in its entirety. But, again, I couldn\’t think of another way of saying what I wanted to, so I was kind of caught between a rock and…. another rock.

I\’ve got other rules as well (which I\’m not going to share right now), but hopefully you\’ll see what I was getting at here, so I crave your forgiveness for giving myself a hard time over this.

And here\’s that post in full –>

Some of you may have seen a fairly unsuccessful but mercifully short demo I did recently about using a CTE to find mismatches between new (source) and old (target) data sets, and then update the target when the data has changed.

The premise was basically to avoid complex JOINs, NOT IN, and various other unsavoury constructs that can cause performance issues, especially when the NOT IN contains a sub-select.

The script looked pretty much like this :

WITH Mismatches (dbName, urlPath)
AS
(SELECT a.dbName, a.urlPath
FROM @t_extract a
INNER JOIN SharepointMaintenance.dbo.SharepointSites b
ON a.urlPath = b.FullURL
INNER JOIN SharepointMaintenance.dbo.ContentDBs c
ON a.dbName = c.Name
WHERE a.dbName != c.Name
AND a.urlPath != \’/\’)
UPDATE SharepointMaintenance.dbo.SiteTracking
SET [dbID] = DB_ID(a.dbName),
extractDate = a.extractDate,
lastDBId = c.ContentDBId,
lastUpdate = GETDATE()
FROM @t_extract a
INNER JOIN SharepointMaintenance.dbo.SharepointSites b
ON a.urlPath = b.FullURL
INNER JOIN SharepointMaintenance.dbo.ContentDBs c
ON a.dbName = c.Name
INNER JOIN Mismatches d
ON b.FullURL = d.urlPath;

So (once you\’ve set up properly), this would update existing rows with changed data, and you\’d still have to cater for completely *new* data somewhere else. Bummer.

Having taken a step back from this for a few weeks, and then having another go (there\’s always a more elegant solution, don\’t you think ?), I thought about using the new SQL2008 MERGE statement.

How hard could it be ? Put very simply, MERGE compares your target and source, works out which ones match, and then does different things for matched and unmatched rows depending on what you tell it to do.

The re-write would look something like the next snippet. The more observant among you will notice that I\’ve fundamentally changed the data that I\’m interested in, but that\’s because my initial analysis was tragically flawed. And it\’s my demo.

MERGE SharepointMaintenance.dbo.SiteTracking AS TARGET
USING (SELECT [dbID], SiteID FROM @t_extract2 ) AS SOURCE ([dbID], SiteID)
ON (TARGET.[SiteID] = SOURCE.[SiteID])
WHEN MATCHED THEN –Update tracking table with last and current dbID
UPDATE SET [dbID] = SOURCE.[dbID],
extractDate = @datenow2,
lastDBId = TARGET.[dbID],
lastUpdate = TARGET.extractDate
WHEN NOT MATCHED THEN –Insert new row to tracking table, so we\’ve got the new site ID
INSERT ([dbID], SiteID, extractDate)
VALUES (SOURCE.[dbID], SOURCE.SiteID, @datenow2);

This second construct does both the UPDATE (for changed data) and the INSERT (for new data), so there\’s no futher coding required.

Apart from all the other stuff you\’ll need to put around it, of course.

Back soon…. (and thanks for reading)