RE: MERGE problem -> when I need to reenter an entry which is already in the history

SSC Guru

Points: 186127

March 5, 2013 at 6:18 am

chingarova (3/5/2013)
ok, lets say that the script look like this:

It's an improvement in that you now have a "key" (not ideal - not everybody has an email address, many folks have several) to identify individuals, but in terms of the overall functionality of the merge statement, it changes little. I honestly think it would be cheaper for you to start again from scratch, laying out the business rules first. There are a number of possible cases when you compare the two tables.

1. Match, Target.IsRowCurrent = 1

2. Match, Target.IsRowCurrent = 0

3. Person in target but not source (WHEN NOT MATCHED BY SOURCE), Target.IsRowCurrent = 1

4. Person in target but not source, Target.IsRowCurrent = 0

5. Person in source but not target

Define fully what you want to do for each case.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden