SQL 2008 T-SQL MERGE Operator

I’ve had some opportunity to play around with a few of SQL 2008’s new features and I really like the new MERGE operator and wrote my own code sample.
-- create the source and target tables
declare @sourceTable table
(
id int primary key identity(1,1) not null,
firstName nvarchar(255) not null,
lastName nvarchar(255) not null
)

declare @targetTable table
(
id int primary key not null,
firstName nvarchar(255) not null,
lastName nvarchar(255) not null
)

-- populate the source
insert @sourceTable
select 'John', 'Doe' union
select 'Jane', 'Doe' union
select 'Adam', 'Smith' union
select 'Alex', 'Smith'

-- view the contents of both tables
select * from @sourceTable
select * from @targetTable

-- use the MERGE statement to populate the target from the source
merge @targetTable t -- target
using (select id, firstName, lastName from @sourceTable) s -- source
on t.id = s.id
when matched then update set t.firstName = s.firstName, t.lastName = s.lastName
when not matched by target then insert values (id, firstName, lastName)
when not matched by source then delete;

select * from @sourceTable
select * from @targetTable

-- Jane Doe and Alex Smith got married and Jane decided to hyphenate her last name to Doe-Smith
update @sourceTable set lastName = N'Doe-Smith'
where firstName = N'Jane' and lastName = N'Doe'

select * from @sourceTable
select * from @targetTable

-- source and target now have different values for the 3rd record
-- we can use the same code to match up target and source
merge @targetTable t -- target
using (select id, firstName, lastName from @sourceTable) s -- source
on t.id = s.id
when matched then update set t.firstName = s.firstName, t.lastName = s.lastName
when not matched by target then insert values (id, firstName, lastName)
when not matched by source then delete;

select * from @sourceTable
select * from @targetTable

-- remove Adam Smith from the source table
delete from @sourceTable where id = 1

select * from @sourceTable
select * from @targetTable

-- once again we'll use the same merge statment to match up our temporary source and target
merge @targetTable t -- target
using (select id, firstName, lastName from @sourceTable) s -- source
on t.id = s.id
when matched then update set t.firstName = s.firstName, t.lastName = s.lastName
when not matched by target then insert values (id, firstName, lastName)
when not matched by source then delete;

select * from @sourceTable
select * from @targetTable

LINQ: Except and Intersect

As of this writing, I’m currently involved in developing a production appliction making use of LINQ, Orcas Beta 2 (Visual Studio 2008), ADO.NET and the .NET 3.5 framework.  I just wanted to put down my recent experience using Except, which is akin to the not in operation in SQL statements.

The task is simple enough.  I have a database containing class/course schedules and a many-to-many relationship table called Registrations that has a uniqueness constraint across two foreign keys, one to the Course table and the other to the Registrant table.  I wanted to be able to present two sets of collections.  One, given a particular course, be able to get the collection of registered Registrants; the second, is a collection of Registrants that are not registered for the course.

In SQL, we’d have something like this:

-- Get registered Registrants
SELECT r.* FROM Registrant AS r JOIN Registrations AS reg ON r.registrantId = reg.registrantId
WHERE reg.courseId = @courseId;

-- Registrants not registered for the course
SELECT * FROM Registrant registrantId NOT IN
(SELECT r.registrantId FROM Registrant AS r JOIN Registrations AS reg ON r.registrantId = reg.registrantId
WHERE reg.courseId = @courseId);

-- Alternate query without using NOT IN
SELECT r.* FROM Registrant AS r LEFT JOIN Registrations AS reg ON r.registrantId = reg.registrantId AND reg.registrantId IS NULL;


With LINQ, the one point I found with Except and Intercept is that the comparison is done at the object level, unlike in SQL where we include/exclude based on the value of a particular property/column.


var regList = (from r1 in Registrant
select r1).Except(from r2 in Registrant
join reg in Registrations on r2.registrantId equals reg.registrantId
where reg.courseId == (new Guid(courseId))
select r2);
/*
Note that in SQL we just needed to specify the registrantId's we want to exclude while with LINQ we need to compare apples to apples.
*/