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
    Else
        Insert Data
    End

    -- 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.

Comments

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

    But... this will save a lot of work !!
  3. Gil 11/15/2007 9:37 AM
    Gravatar
    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
    Gravatar
    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
    Gravatar
    Article is so nice, here I listed some new features of Visual Studio 2008

    50 New Features of SQL Server 2008 - Microsoft .NET Support
    http://msdotnetsupport.blogspot.com/2008/11/sql-server-2008-new-features.html
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections