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.
