How to understand the MERGE keyword with SQL Server 2008

I have a basic understanding of the MERGE syntax for SQL Sever 2008 but have a question about an advanced use of the concept.

Imagine I have a sports team that has 30 players in it. Each player is a record in a table. Now, half way through the season, some players have left (injury, whatever) with some new blood arrived to replace them.

So开发者_运维知识库 I wish to update the database table to accurately reflect this change.

So when I use the MERGE syntax, if a new player doesn't exist, then it gets inserted:

WHEN NOT MATCHED THEN
    INSERT blah blah blah...
    VALUES (blah blah and more blah)..

and when matched, then do nothing (so I don't need to have any WHEN MATCHED THEN clause).

But what about those players who have left? Is there where the WHEN NOT MATCHED BY SOURCE THEN <merge_matched> comes in to play?

If so, how should I use it?

You can use merge like that but then I guess that your source table for the new team must hold the entire new team including those that has not been modified. Here is a simplified sample.

The season starts out with this team

PlayerName           Position
-------------------- --------
Brett Favre          QB
Joe Montana          QB
John Elway           QB

Half way through the season the coach realizes that it is not optimal to have a team with three rather old QB's. Brett refuses to quit so Joe has to go. John gets to play RB and at the same time we need some youngsters to get things going.

PlayerName           Position
-------------------- --------
Brett Favre          QB
John Elway           RB
Jerry Rice           WR
Karl Mecklenburg     LB

The merge statement to merge NewTeam to Team would look like this.

merge Team as T
using NewTeam as S
on S.PlayerName = T.PlayerName
when matched then
  update set Position = S.Position
when not matched by target then
  insert (PlayerName, Position) values (S.PlayerName, S.Position)
when not matched by source then
  delete;  

Yes, WHEN NOT MATCHED BY SOURCE THEN <merge_matched> is the section where you can take care about the players that have left.

According to the doc, you can use an UPDATE (for example, set the Active atribute to 0) or a DELETE instruction there.

You can find out from the documentation that there can be two such sections. In that case one of them is defined with an additional condition and can only accept an UPDATE instruction:

WHEN NOT MATCHED BY SOURCE AND condition THEN
    UPDATE SET ...

The other must not use a condition and is reserved for a DELETE instruction.