THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

SQL Server 2008's MERGE Statement

SQL Server 2008 provided us with some new T-SQL syntax; one of those that I am most excited about is the new MERGE statement. Gone are the days of IF....THEN logic to decide whether a row needs to be inserted, updated, or deleted. The MERGE allows you to take care of the logic and the insert all in one shot. What's more, you can compare an entire record set all at once instead of going row by row. Here's is a quick example of using MERGE.

MERGE tbl_address AS current_addresses
USING(
SELECT customer_objid = address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted
FROM @addresses)
AS
source_addresses(address_label, addressline1, addressline2, city, region, country, zipcode, is_deleted)
ON
(current_addresses.address_label = source_addresses.address_label)
WHEN NOT MATCHED THEN
INSERT (address_label, addressline1, addressline2, city, region, country, zipcode)
VALUES (source_addresses.address_label, source_addresses.addressline1, source_addresses.addressline2, source_addresses.city, source_addresses.region, source_addresses.country, source_addresses.zipcode)

WHEN MATCHED AND source_addresses.is_deleted = 1 THEN
DELETE

WHEN MATCHED THEN
UPDATE
SET address_label=source_addresses.address_label, addressline1=source_addresses.addressline1, addressline2=source_addresses.addressline2, city=source_addresses.city, region=source_addresses.region, country=source_addresses.country, zipcode=source_addresses.zipcode;

The USING section defines the "new" data, in this case a table variable. The ON section defines the join between the new and the existing data. Finally you can have a series of MATCHED statements that do things like insert WHEN NOT MATCHED, update WHEN MATCHED, or delete WHEN MATCHED and some other values indicates delete. The possibilities are endless and the syntax is pretty clean. So take the time and experiment with MERGE, it will save you a little effort when conditionally updating and inserting data.

Published Thursday, March 04, 2010 10:00 AM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John Q. Newman said:

Yes, ladies and gentlemen, another satisfied customer of my world-famous "Code Formatting for Job Security" course!

March 4, 2010 11:42 AM
 

Mr. E said:

@John +1

March 4, 2010 12:06 PM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement