SQL Server 2008 MERGE statement

The MERGE statement in SQL Server 2008 is one of the more anticipated features to come along in awhile without question; as I have begun to work with it over the past few days, I have started to note a few things while using:

1. Primary key on the source - your source table will have to have a primary key. To me, this is a change in the order of processing that I am used to for data loads. As a general rule I like to load whatever is given in the source, and then clean it up when importing to the target. Using MERGE, the source must be already cleaned; the MERGE statement isn't smart enough to be able to handle two similar records. This might be a problem for some implementations. For instance, let's say that x number of stores make a change to an attribute of some sort. The logic to limit the final attribute value for the merge will have to occur before actually running the statement from the source load table or it will fail.
   
   
2. Using for data mart/data warehouse loads - Ben-Gan [1], SQL Server Magazine's whiz-bang and Microsoft MVP states the following:
   
   Examples from a data warehouse environment include handling slowly changing dimensions and merging fact additions and changes into a target fact table as part of your daily extraction, transformation, and loading (ETL) process..
   
   Normally, I find this to not be recommended or chosen method for handling dimensions, only covering the "Type-1" dimension change (updating). Remember that Type-2 is the preferred method, and Type-3 is also recommended for capturing current and most recent value for a dimension. MERGE can handle Type-1 and Type-2; the problem is that it does it simultaneously, and normally you want either one or the other.
   
   
3. Must use a semicolon - Maybe a good argument to end all statements with a semicolon in SQL Server 2008.

I am going to create some examples, hopefully beyond the basic ones that you see in BOL and other sites, and dig in to this really cool addition to the TSQL syntax.

Lee

-----------------------

That'd be a cool site - substantial examples beyond those in Books Online. Need to work on that one...

1. [Ben-Gan, 2008]. Merge. http://www.sqlmag.com/Article/ArticleID/97963/MERGE.html