Changes to MERGE and HIERARCHYID
Itzik describes changes to the MERGE statement and the HIERARCHYID datatype that will appear after CTP 6.
April 23, 2008
For those of you who already had the chance to play with the MERGE statement and the HIERARCHYID datatype in Microsoft SQL Server 2008, you might be interested to know that Microsoft made a few changes to those that will show up after CTP 6 (CTP Refresh and of course RTM).
Changes to MERGE
In CTP 6 the MERGE statement supports the following clauses (among others):
WHEN [TARGET] NOT MATCHED THEN
WHEN SOURCE NOT MATCHED THEN
Due to the efforts of Taylen Wong, Microsoft decided to change these two clauses. Taylen submitted to Microsoft that the existing syntax is ambiguous. Namely, that it is not really clear in the existing syntax which clause represents the case where a source row has no match in the target table, and which where a target row has no match in the source table.
In the next public CTP (CTP Refresh) and of course in RTM, Microsoft will change the syntax of the above clauses to the following nonambiguous syntax:
WHEN NOT MATCHED [BY TARGET] THEN
WHEN NOT MATCHED BY SOURCE THEN
For example, consider the following MERGE statement that uses the CTP 6 syntax:
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN TARGET NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN SOURCE NOT MATCHED THEN
DELETE;
After CTP 6, the valid syntax for this statement will be:
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED BY TARGET THEN
INSERT (custid, companyname, phone, address)