First, I owe our proprietor here a review of Ignite Free or whatever from Confio. Here's the quick review: It's like liquid awesome poured into a coffee cup, ready to drink. Took 5 minutes to set up, 5 minutes to realize "holy crap I need to get this pointed at production NOW!". 5 minutes to ascertain the server load caused by monitoring (almost none). 1 minute to get it pointed to production, and then 5 minutes to start finding and fixing horrible queries. The biggest part of a performance tuning task is finding out what's slow. This tool DOES THAT FOR YOU. In a way that's complete, inobtrusive, fast, accurate, and amazing. If you're not using this tool, you're a tool.
Second, I want do a nice writeup of Change Data Capture, which looks just unbelievably good. This one feature is good enough to justify an Enterprise Edition license. Seriously. It's that good. I'm gonna get this project done and then do a review for y'all.
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.