May 07, 2015

Have you ever…?

These three words at the beginning of a sentence usually mean that the person asking has done something in the past that they're not quite sure of. Maybe it's something they've done that they weren't sure was legal (or ethical), or maybe they weren't sure it was done correctly. Now I'm not casting nasturtiums here, gentle reader, I'm just easing myself into letting you know that I, yes me, may have done things in the past that I'm not terribly proud of.

Gasp! Yes, I heard that collective intake of breathe. Not you Adam, surely. Yes, but stop calling me Shirley. Sorry, couldn't help myself. Anyway, you'll notice I'm still circling around my confession. Well, here goes…

Okay, in the past, I've written SQL code that wasn't as elegantly composed as it could have been. There! I've said it. So, I return to my original question, have you ever? Well, yes, I have. Let me explain.

The other day, I was looking over some old SQL code I had written some years ago. Have you ever… cringed at your own old code? I certainly did. Well, the code in question was something I'd written to either insert new rows or update existing rows. It's a pretty familiar kind of situation. You first check to see if a row exists, if it does, you run an update statement, if it doesn't, you insert a new record. Simple, right? Yep, usually. But you know, SQL moves along, it doesn't stand still, it evolves over time if you like. What may have been a familiar way of doing things in the past becomes obsolete with the introduction of new statements or clauses. And that's exactly what happened.

When Microsoft released SQL Server 2008 (yep, that's a long time ago now), they introduced the MERGE statement. This single statement made all of that logic I'd written before completely pointless, long-winded and just downright inelegant. Yuck!

So, let's examine this mythical beast and see what it can do for us. Basically, the MERGE statement allows you to compare two tables and determine if an INSERT, UPDATE and/or a DELETE operation needs to be performed against one of the tables. It can also make use of the TOP clause to only perform a specific number of operations. Additionally, it can also OUTPUT information so you know specifically which rows/values were inserted, updated or deleted. VERY cool.

I reckon the best way to understand this is with an example. This example is a process commonly called “UPSERT” (UPDATE/INSERT). We will need a “Source” and a “Target” table. Obviously, the “Target” is where the records will be updated or inserted, and the “Source” is where the records come from. So, the first thing to do is to create a couple of tables to muck around with:

Alrighty then, so dbo.Sales will be the target for my merge operation, and dbo.NewSales is the source of the rows to insert or update.

If you compare the records from both tables you’ll notice that there is a record with Id=1 in both, but the value for SalesAmount is different. You’ll also notice that the source table has an additional row. I want my MERGE logic to update the existing row and insert the new row. The Id will determine if there is a MATCH or not. So the statement I would write might look something like this:

And the result should look somewhat akin to:

Okay, so my example is only meant to whet your appetite (that’s right, I said ‘whet’). It’s not intended as the unexpurgated version, for that you can visit Books Online for the complete low-down. But hopefully this quick squiz has given you some sense of the power of this new-ish statement. It might even, dare I say it, make your code a little more elegant, ‘cos that’s what we’re after.

Cheers for now, see you next time.