There are several features coming in SQL Server 2008 (and in the June CTP) that are in high demand in the ISV community, and so I thought I would introduce a few of those features here.

First I thought I would give a real basic intro to the new T-SQL MERGE command, which allows you to specify one T-SQL command that can either update a record in a target table if it already exists or insert one if it is missing. The command is actually very powerful allowing you to specify rich matching conditions and also to update or delete target table records that are missing from a source row set.

These features allow you to build very rich table synchronization scenarios. Check out the books online in the CTP for more details.

For now, I'll show you a very basic example.

Let's start with our target table, Profiles, that keeps various bits of information about my customers:

ProfileName ZipCode FavoriteFlavor LastVisit

----------- -------- -------------- -----------------------

Alice 10101 Vanilla 2007-06-01 15:00:00.000

Bill NULL Strawberry 2007-05-31 14:00:00.000

Charlie 10102 NULL NULL

In this scenario, we collect Zip code information from a 3rd party source into a table called NewLocationData, and want to merge that information with the profiles we already have. Some these records should cause updates; others should be inserted as new profile records.

ProfileName ZipCode

------------- -------

Alice 10105

Douglas 10108

Earl 10109

 

Using the new T-SQL MERGE command, we can combine these tables in a single command:

MERGE INTO Profiles

USING NewLocationData

ON (Profiles.ProfileName = NewLocationData.ProfileName)

WHEN MATCHED THEN

UPDATE SET Profiles.ZipCode = NewLocationData.ZipCode

WHEN TARGET NOT MATCHED THEN

    INSERT (ProfileName, ZipCode) VALUES (NewLocationData.ProfileName, NewLocationData.ZipCode);

As you can see, we specify the target and source tables as well as the criteria we should use to match records (in this case, Profiles.ProfileName = NewLocationData.ProfileName). We then provide commands to be run when records match or when they do not match.

When we run this command, the result is (3 row(s) affected) and afterward the Profiles table looks like this:

ProfileName ZipCode FavoriteFlavor LastVisit

----------- -------- -------------- -----------------------

Alice 10105 Vanilla 2007-06-01 15:00:00.000

Bill NULL Strawberry 2007-05-31 14:00:00.000

Charlie 10102 NULL NULL

Douglas 10108 NULL NULL

Earl 10109 NULL NULL

 

As you can see, the existing "Alice" record was updated, and the new "Douglas" and "Earl" records were inserted.

Next time, we'll look at the new Change Data Capture functionality…