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

SSC Guru

Points: 186127

March 5, 2013 at 3:05 am

chingarova (3/5/2013)
OK, let me explain...
The script I posted is just an example(my real example is different and much more complex,but this doesn't matter), what I need to understand is why when we have a MERGE STATEMENT WHICH KEEPS HISTORY, WHEN AN ENTRY IS OBSOLETE AND THEN IT IS ADDED AGAIN IN THE UPDATE TABLE, IT IS NOT UPDATED IN THE MAIN TABLE AND IT IS STILL SHOWN AS OBSOLETE....if you have suggestion regarding this, it will helps. Thanks

Please don't shout.

Every successful update

WHEN MATCHED and Target.IsRowCurrent = 1 -- SCD 1/2 changes

AND

(

Target.FirstName <> Source.FirstName

OR Target.LastName <> Source.LastName

OR Target.Title <> Source.Title

)

THEN UPDATE SET

will cause a duplicate row (duplicate on firstname) to be inserted into the table, via the OUTPUT clause.

There are way too many serious logic flaws in the posted example script for it to be of any use. Can you post the actual merge statement you are using?

^{“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