BT

MERGE Syntax for SQL Server 2008

| by Jonathan Allen Follow 577 Followers on Jul 18, 2007. Estimated reading time: 1 minute |

SQL Server 2008 will include new syntax for merging data between two rowsets. The MERGE statement allows developers to use one command to perform deterministic inserts, updates, and deletes on a table based on a source table.

When synchronizing some information across two tables, up to three operations have to be performed. First, any new rows need to be inserted into the target table. Then existing rows have to be updated. Finally, old rows no longer in use may need to be deleted. This can lead to a lot of repetitive logic which needs to be maintained, and in turn can lead to subtle bugs.

Bob Beauchemin discusses the MERGE statement, which combines all these actions into a single statement. The example he gives is:

merge [target] t
using [source] s on t.id = s.id
when matched then update t.name = s.name, t.age = s.age -- use "rowset1"
when not matched then insert values(id,name,age) -- use "rowset2"
when source not matched then delete; -- use "rowset3"

As you can see, the action being performed is dependent on the join was resolved. In this example, and update occurs if the target and source have matching row. If they don't match, an insert or delete is performed to bring the destination in line with the source.

One nice feature of this syntax is that it is deterministic when it comes to updates. When using the standard UPDATE syntax with joins, it is possible that more than one source row matches the target row. If this occurs, there is no way to predict which source row will be used for the update.

When using the MERGE syntax, an error will be raised if there are multiple matches. This informs the developer that the join is not explicit enough for what they are trying to accomplish.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT