Modify data by using MERGE statements
May include but is not limited to: INSERTED, DELETED, OUTPUT
Merge is a shiny new feature in SQL Server 2008, which lets you specify complex, conditioned data modifications within a single statement. For the minimum, you need to include the MERGE keyword, the target and the source tables, a condition on how to match those tables, and what should happen when matches are found/not found. I’m quite sure that a code sample would be much more explanatory, so here’s one:
MERGE Customers AS target
USING Agents AS source
ON (target.CustomerID = source.CustomerID)
WHEN MATCHED THEN
SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (CustomerID, CustomerName)
VALUES (source.CustomerID, source.CustomerName)
I have deliberately put the semicolon on a new line, because it’s necessary to close your MERGE commands with them.
Furthermore, you can specify the matches you are interested in. This means you can write a WHEN NOT MATCHED BY SOURCE/TARGET THEN statement. It’s quite straightforward, but I’d mention here that you cannot issue a DELETE or UPDATE command when you don’t match by target, and you cannot issue an INSERT when you don’t match by source.
The MERGE command is a very powerful tool, make sure you use it regularly.