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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
<span style="font-family: Lucida Console;"><span style="color: #008000;">--// Create our SOURCE table</span> <span style="color: #0000ff;">DECLARE </span><span style="color: #000000;">@t1 </span><span style="color: #0000ff;">TABLE </span><span style="color: #000000;">(</span> <span style="color: #000000;">id </span><span style="color: #008080;">int</span><span style="color: #000000;">,</span> <span style="color: #000000;">name </span><span style="color: #008080;">nvarchar</span><span style="color: #000000;">(16),</span> <span style="color: #000000;">email </span><span style="color: #008080;">nvarchar</span><span style="color: #000000;">(32)</span> <span style="color: #000000;">)</span></span> <span style="font-family: Lucida Console;"><span style="color: #008000;">--// Create our TARGET table</span> <span style="color: #0000ff;">DECLARE </span><span style="color: #000000;">@t2 </span><span style="color: #0000ff;">TABLE </span><span style="color: #000000;">(</span> <span style="color: #000000;">id </span><span style="color: #008080;">int</span><span style="color: #000000;">,</span> <span style="color: #000000;">name </span><span style="color: #008080;">nvarchar</span><span style="color: #000000;">(16),</span> <span style="color: #000000;">email </span><span style="color: #008080;">nvarchar</span><span style="color: #000000;">(32)</span> <span style="color: #000000;">)</span></span> <span style="font-family: Lucida Console;"><span style="color: #008000;">--// Insert values into SOURCE table</span> <span style="color: #0000ff;">INSERT INTO </span><span style="color: #000000;">@t1 </span><span style="color: #0000ff;">VALUES </span><span style="color: #000000;">(1, </span><span style="color: #ff00ff;">'Charles Chen'</span><span style="color: #000000;">, </span><span style="color: #ff00ff;">'charles.chen@domain.com'</span><span style="color: #000000;">)</span> <span style="color: #0000ff;">INSERT INTO </span><span style="color: #000000;">@t1 </span><span style="color: #0000ff;">VALUES </span><span style="color: #000000;">(2, </span><span style="color: #ff00ff;">'Sandra Chen'</span><span style="color: #000000;">, </span><span style="color: #ff00ff;">'sandra.chen@domain.com'</span><span style="color: #000000;">)</span> <span style="color: #0000ff;">INSERT INTO </span><span style="color: #000000;">@t1 </span><span style="color: #0000ff;">VALUES </span><span style="color: #000000;">(3, </span><span style="color: #ff00ff;">'Brad Wright'</span><span style="color: #000000;">, </span><span style="color: #ff00ff;">'brad.wright@domain.com'</span><span style="color: #000000;">)</span></span> <span style="font-family: Lucida Console;"><span style="color: #008000;">--// Insert values into TARGET table</span> <span style="color: #0000ff;">INSERT INTO </span><span style="color: #000000;">@t2 </span><span style="color: #0000ff;">VALUES </span><span style="color: #000000;">(4, </span><span style="color: #ff00ff;">'Brady Sines'</span><span style="color: #000000;">, </span><span style="color: #ff00ff;">'brady.sines@domain.com'</span><span style="color: #000000;">)</span> <span style="color: #0000ff;">INSERT INTO </span><span style="color: #000000;">@t2 </span><span style="color: #0000ff;">VALUES </span><span style="color: #000000;">(1, </span><span style="color: #ff00ff;">''</span><span style="color: #000000;">, </span><span style="color: #ff00ff;">''</span><span style="color: #000000;">)</span></span> <span style="font-family: Lucida Console;"><span style="color: #008000;">--// Select values from the TARGET table (just to verify)</span> <span style="color: #0000ff;">SELECT </span><span style="color: #000000;">* </span><span style="color: #0000ff;">FROM </span><span style="color: #000000;">@t2</span></span> <span style="font-family: Lucida Console;"><span style="color: #008000;">--// Merge records from source to target</span> <span style="color: #0000ff;">MERGE</span> <span style="color: #000000;">@t2 </span><span style="color: #0000ff;">AS </span><span style="color: #000000;">[target]</span> <span style="color: #0000ff;">USING</span> <span style="color: #000000;">@t1 </span><span style="color: #0000ff;">AS </span><span style="color: #000000;">[source]</span> <span style="color: #0000ff;">ON</span> <span style="color: #000000;">([target].id = [source].id)</span> <span style="color: #0000ff;">WHEN NOT MATCHED BY TARGET</span> <span style="color: #0000ff;">THEN</span> <span style="color: #0000ff;">INSERT</span><span style="color: #000000;">(id, name, email)</span> <span style="color: #0000ff;">VALUES</span><span style="color: #000000;">([source].id, [source].name, [source].email)</span> <span style="color: #0000ff;">WHEN MATCHED</span> <span style="color: #0000ff;">THEN</span> <span style="color: #0000ff;">UPDATE SET</span> <span style="color: #000000;">[target].name = [source].name,</span> <span style="color: #000000;">[target].email = [source].email</span> <span style="color: #008000;">--// Get the journal of actions</span> <span style="color: #0000ff;">OUTPUT </span><span style="color: #000000;">$action, inserted.*, deleted.*;</span></span> <span style="font-family: Lucida Console;"><span style="color: #008000;">--// Grab the resultant table</span> <span style="color: #0000ff;">SELECT </span><span style="color: #000000;">* </span><span style="color: #0000ff;">FROM </span><span style="color: #000000;">@t2</span> </span> |
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 😀
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.
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.
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