Chris Miller Blog

Looking on teh interwebs for some example of how to write a very simple MERGE statement.  I haven't done much with MERGE.  I want to update one column, one row, from a variable.  If the row exists, update, if the row doesn't exist, insert it.  This is, in my mind, the bread-and-butter MERGE.  What MERGE was written to do.  Yeah, it'll do a ton more than that, but the very simplest case, the degenerate case, would be something stupidly simple.  There are a bunch of really simple people on teh interwebs, I'll ask Mr Google how to do this.  I'm sure someone's covered it…

Every single example I found on the internet was a regurgitation of the one in Books Online.  Every Last One.  Blog posts by renown MVP's, social.msdn posts from people who want to be MVP's.  Basically cribbing BOL for content and changing some column/table names around.  It was funny and sad at the same time.  Wow, really, you can do a join in a MERGE?  Fascinating.  But that's something I'll do once in a blue moon, why did you pick that example Mr Blog Post Author?  Oh, because it's the one in BOL and was the least amount of work.  Sigh.  Nobody really explained what every line of MERGE does.  Because that would be useful and involve work and wouldn't be easy to search engine optimize, so wouldn't provide revenue. 

Since the proprietor here pays me a flat rate in beer, I don't care about revenue.  So here ya go, actual useful content.

declare @From_LSN_Leadraw binary(10)

SET @From_LSN_Leadraw =  sys.fn_cdc_get_min_lsn('dbo_leadraw')

        merge into CDCTransactionTracking

        Using (select @From_LSN_Leadraw From_LSN_Leadraw) Source

        on CDCTransactionTracking.Tablename = 'LeadRaw'

        when matched then update set MinLSN = Source.From_LSN_LeadRaw

        when not matched then insert (TableName, MinLSN) values ('LeadRaw', Source.From_LSN_LeadRaw);

So let's break this down. 

First, specify the target table, in this case CDCTransactionTracking.  I could alias this to a name like Target, but this is an engineering POC, not pretty production code.  The next line specifies the data source.  You can't source from a scalar variable, so this does a select and creates a little quickie rowset that is enough to fool the compiler and let it run. 

Yes you could use a table variable here.  Every time you create a table variable, god kills a kitten.  I don't particularly like cats, but that's just unnecessarily cruel.

Next, the ON clause.  We need to define what's going to join the target and the source.  In this case, that's a constant.

After that, what should happen when the statements match.  When that happens I just want to do an update.  Easy peasy.  That line could also read set MinLSN = @From_LSN_Leadraw.  Same thing.

Then when we don't match, we want to do an insert.  Pretty straightforward.  Again, could do @From_LSN_Leadraw.

There ya go.  Original work.  On the internet.  No kittens harmed or copy/paste/Search/Replace from BOL.