RSS 2.0 Feed
RSS 2.0

Atom 1.0 Feed
Atom 1.0

  MERGE in SQL 2008 

A co-worker pointed out to me something cool that is coming in SQL 2008. A new keyword for MERGE has been introduced. This new type of statement reduces some of the tedious work you typically do when adding data to a table in SQL. As things are now, you will check for the existence of a row, and then proceed to update the matched row if it exists, or insert the new data if the matching row did not exist. The new MERGE keyword allows you to do this all in a single statement.

This new MERGE statement reduces the following pseudo code:

    -- save parent table data
    If Data Indentifier found in Parent Data Table
        Update Data
        Insert Data

    -- save child table data
    Delete from Child Data Table all items not in Data
    Update Child Data Table with all items in Data
    Insert into Child Data Table all new items in Data

To the following:

    -- save parent table data
    MERGE Data to Parent Data Table

    -- save child table data
    MERGE Data to Chid Data Table


Leave a comment below.


  1. Jason Haley 9/13/2007 9:33 AM
  2. Daniel Bloch 10/23/2007 12:32 AM
    how about the performance ...

    But... this will save a lot of work !!
  3. Gil 11/15/2007 9:37 AM
    Turns out the syntax is not quite that simple.

    You have to tell it how to join the tables, specify column comparisons to determine when columns of interest have changed (to avoid always doing an UPDATE on matched rows), specify the actions to take (INSERT, UPDATE or DELETE) in a case-like structure for each condition (matched, source not matched in target, target not matched in source).

    But, it IS powerful and it does perform well (only has to process the tables once, rather than 3 times if you did separate insert, update, deletes), handles triggers reasonably, etc.
  4. Forrest 1/17/2008 8:31 PM
    I'm with Gil on this one ( about performance ) although even with three trips to the data store, at least the data pages ought to be cached. Still, this should perform better, and it gets rid of SO MUCH scaffolding code!
  5. Dutt 11/8/2008 2:32 AM
    Article is so nice, here I listed some new features of Visual Studio 2008

    50 New Features of SQL Server 2008 - Microsoft .NET Support
Comments have been closed on this topic.



Also see my CRM Developer blog