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
                UPDATE
                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.

Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: