<CharlieDigital/> Programming, Politics, and uhh…pineapples

5Jun/09Off

T-SQL MERGE: My New Friend

As I was catching up on SQL 2008 this morning, I spent some time working with the T-SQL MERGE command, new in SQL 2008. Most of the blogs and examples online are a bit terse, so I decided to write a simple sample.

Check it out:

--// Create our SOURCE table
DECLARE @t1 TABLE (
    id      int,
    name    nvarchar(16),
    email   nvarchar(32)
)
 

--// Create our TARGET table
DECLARE @t2 TABLE (
    id      int,
    name    nvarchar(16),
    email   nvarchar(32)
)

--// Insert values into SOURCE table
INSERT INTO @t1 VALUES (1, 'Charles Chen', 'charles.chen@domain.com')
INSERT INTO @t1 VALUES (2, 'Sandra Chen', 'sandra.chen@domain.com')
INSERT INTO @t1 VALUES (3, 'Brad Wright', 'brad.wright@domain.com')

--// Insert values into TARGET table
INSERT INTO @t2 VALUES (4, 'Brady Sines', 'brady.sines@domain.com')
INSERT INTO @t2 VALUES (1, '', '')

--// Select values from the TARGET table (just to verify)
SELECT * FROM @t2

--// Merge records from source to target
MERGE
    @t2 AS [target]
USING
    @t1 AS [source]
ON
    ([target].id = [source].id)
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT(id, name, email)
        VALUES([source].id, [source].name, [source].email)
WHEN MATCHED
    THEN
        UPDATE SET
            [target].name = [source].name,
            [target].email = [source].email
--// Get the journal of actions
OUTPUT $action, inserted.*, deleted.*;

--// Grab the resultant table
SELECT * FROM @t2

And here are the results:

Awesome! Admittedly, I'm still trying to come up with some legitimate uses for this in my day-to-day application programming 😀

Posted by Charles Chen

Filed under: SQL Server Comments Off
Comments (3) Trackbacks (0)
  1. MERGE is especially useful for loading datawarehouses.
    one use i’ve found fo rin normal app developemnt is the
    if exists update
    else insert
    problem which merge solves beautifully.

  2. Yeah, that did pop up in my mind, but it seemed like it would be overkill since it would involve creating a table variable just to get it to work.

    I’ve usually handled this just by doing an EXISTS first and branching (INSERT or UPDATE) depending on if the object ID already exists.

  3. yeah i did the same thing, but it has it’s problems:
    http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx
    and here’s the comparison for Merge:
    http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx

    and to be complete a way to get total pessimistic concurrencything out of the way.
    Application Locks (or Mutexes) in SQL Server 2005:
    http://weblogs.sqlteam.com/mladenp/archive/2008/01/08/Application-Locks-or-Mutexes-in-SQL-Server-2005.aspx


Trackbacks are disabled.