Are you a startup?
Get BizSpark cloud access
Get up to $3,700 of cloud benefits
Don’t have MSDN?
Here’s cloud access
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.
Using the new T-SQL MERGE command, we can combine these tables in a single command:
(Profiles.ProfileName = NewLocationData.ProfileName)
SET Profiles.ZipCode = 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:
Alice 10105 Vanilla 2007-06-01 15:00:00.000
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…