One of the new features in CTP3 is the merge command. This was introduced in the ISO SQL:2003 revision of the SQL language definition. Some readers may be familiar with the term upsert which provides similar functionality in other databases.

A common use of merge would be where a new set of data needs to be added to an existing fact table. Often the new data will contain a mixture of updates, to pre-existing records as well as totally new data. The conventional solution would be to split the new data into two groups. The new records can simply be inserted into the fact table while the updated data needs to be joined with the fact table and the existing fact columns updated with the new values.

What the merge command provides is a mechanism to perform these two operations with one single command, together with a commensurate improvement in performance. Having worked with several partners who regularly "merge" tens of millions of records into fact tables this feature has the potential to improve performance considerably.

So what does the syntax look like? Well here is an example from the BOL:-

MERGE FactBuyingHabits AS fbh
USING (SELECT CustomerID, ProductID, PurchaseDate FROM PurchaseRecords) AS src
ON (fbh.ProductID = src.ProductID AND fbh.CustomerID = src.CustomerID)
WHEN MATCHED THEN
    UPDATE SET fbh.LastPurchaseDate = src.PurchaseDate
WHEN NOT MATCHED THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
        VALUES (src.CustomerID, src.ProductID, src.PurchaseDate);

Pulling this statement apart we can see that we are merging data into an existing table called FactBuyingHabits. The new data is a query from the PurchaseRecords table. The on clause states which columns need to match between the two tables. The rest of the syntax then defines what happens when there is either a match or no match. In this case we either update the approprate columns with new values, or create a new record in the FactBuyingHabits table from the unmatched record.

There is a considerable flexibility in the above statement. For example instead of issuing an UPDATE statement when a match is found you can DELETE the record from the existing table. Its also possible to have two clauses for either the matched or not matched conditions which allows an additional condition to determine the action. An example could be when we match we will delete the record if the new quantity is zero but update the record if the quantity is non-zero.

 If the merge command sounds interesting then download the CTP, read the books online, and have a play with it yourself. Let me know how you get on.