I am easily excited, a nice calculator watch can keep me occupied for hours on end, but that aside, the new MERGE statement in SQL Server 2008 makes me a little giddy. 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 a 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. Have fun.
Digg It