In an effort to get up to speed on SQL Server 2008, I decided to write a series of posts on some of the new features, as and when they are included in the CTP releases. This post relates to the new SQL Merge statement. Now, I say new but this was included within SQL Server 2005 Beta 2 however was pulled from RTM. This kind of thing is one of the reasons why the team are only including near-complete features in the CTP.
The feature allows you to merge two tables together based on a set of criteria. The Merge works by stating a table where all the data should be merged into; the table, or query, where the data is coming from; and the criteria to decide if they match. Based on this, the query can perform an action for each row such as insert, update or delete. However, the action performed depends on the result of a row being matched from one table to the other. There are three different matched clauses:
- WHEN MATCHED THEN
- Rows that meet the criteria.
- WHEN [TARGET] NOT MATCHED THEN
- Rows that do not match another row in the target table
- WHEN SOURCE NOT MATCHED THEN
- Rows that do not match another row in the source table
You can also set additional conditions on the matched clause, with different outcomes for each clause, for example
WHEN MATCHED AND tab.aValue > 100 THEN
WHEN MATCHED AND tab.aValue < 100 THEN
This example will demonstrate how the MERGE statement works. Nothing interesting but helps the explanation.
Firstly, I have created two tables and inserted two rows into each table.
CREATE TABLE DataTable1 (ID int, DataValue varchar(50), Msg varchar(50))
CREATE TABLE DataTable2 (ID int, DataValue varchar(50), Msg varchar(50))
INSERT INTO DataTable1 VALUES (1, ‘TDV1’, ‘Inserted’), (2, ‘TDV2’, ‘Inserted’)
INSERT INTO DataTable2 VALUES (1, ‘TDV3’, ‘Inserted’), (3, ‘TDV4’, ‘Inserted’)
This is when the Merge query to merge DataTable1 into DataTable2.
MERGE DataTable2 AS mainData
USING (SELECT ID, DataValue FROM DataTable1) otherData
ON (mainData.ID = otherData.ID)
WHEN MATCHED THEN UPDATE SET Msg = ‘MATCHED’
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, DataValue, ‘TARGET NOT MATCHED’)
WHEN SOURCE NOT MATCHED THEN UPDATE SET Msg = ‘SOURCE NOT MATCHED’;
Breaking this down:
MERGE defines the target table which is the target for any insert, updates or deletes which are defined in the matched clauses.
USING specifics the source table.
ON specifics the match condition.
When a row is matched, we change the message in the row from Inserted to Matched.
If the row (in the source table) does not match another row in the target table, we insert the row into the target table and set the message to Target not matched
If the row (in the target table) does not match another row in the source table, we update the message to Source not matched.
The actual statement which can be executed with the matched block has some restrictions, for example I couldn’t do WHEN TARGET NOT MATCHED THEN DELETE.
The result of this is merge is that DataTable2 has three rows:
1 TDV3 MATCHED
2 TDV2 TARGET NOT MATCHED
3 TDV4 SOURCE NOT MATCHED
Because we are matching on ID, ID=1 matched in both tables and because all changes affect the target table, the TDV3 row was kept and updated disregarding TDV1 row. TDV2 was in our source table, however did not have a relating row in the target table and so was caught by our Target not matched clause. Finally, TDV4 was in our target table, but no row in the source data being merged in resulting in it failing under the source not matched clause.
In summary, this is a useful feature. Going to be a great help for people managing data warehouses. It would be useful for synchronizing data, however I think there are more appropriate tools for that now (or to be) included.
Reference in SQL BOL (Books Online) : ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/c17996d6-56a6-482f-80d8-086a3423eecc.htm
Full SQL Query Statement : http://blog.benhall.me.uk/Code/Sql/SqlMerge.txt