chingarova (3/5/2013)
The business key is the FirstName. I am merging the initial table with update tables and I am doing this based on the FirstName(which will not chanege in my case, only the LastName may change -for example if Anna gets married). But I don't think this is the focus of my question...Thanks
So long as there aren't more than half a dozen or so employees in the table, that will be just Jim Dandy. A few years ago I worked for a small software house with 26 people on site, of whom 7 were called Chris (I lie - one of them was Kris). It really isn't going to work, is it? Accounting for an edge case - surname change on marriage - is completely screwing up your logic. Take the edge case out of this equation entirely, deal with them with separate code. All you need is an update!
Back to the majority. Use Firstname & Lastname as business key, it will make your merge logic much simpler.
--Create an update table to hold new/updated persons
The newpersons table would have to contain all retained persons in addition to changes, otherwise WHEN NOT MATCHED BY SOURCE clause will mark them as obsolete.
WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes
AND
(
Target.FirstName <> Source.FirstName
Why? They're matched on Firstname.
Personally, I'd throw this merge statement away and start again - after first rewriting the business rules.
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