The Power of Merge

MERGE is one of the more powerful statements in the T-SQL repertoire.

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.

Let's look at an example of a 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, it's 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 indicating deletions. The possibilities are endless and the syntax is pretty clean.

Take the time and experiment with MERGE. It will save you a little effort when conditionally updating and inserting data.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Microsoft Warns SameSite Cookie Changes Could Break Some Apps

    IT pros could face Web application issues as early as next month with the implementation of a coming SameSite Web change, which will affect how cookies are used across sites.

  • Populating a SharePoint Document Library by E-Mail, Part 1

    While Microsoft doesn't allow you to build a SharePoint Online document library using e-mail, there is a roundabout way of getting the job done using the tools that are included with Office 365. Brien shows you how.

  • Microsoft Previews New App Reporting and Consent Tools in Azure AD

    Microsoft last week described a few Azure Active Directory improvements for organizations wanting to connect their applications to Microsoft's identity and access service.

  • Free Software Foundation Asks Microsoft To Release Windows 7 Code

    The Free Software Foundation this week announced that it has established a petition demanding that Microsoft release its proprietary Windows 7 code as free software.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.