• Let's say you have a "dupe pair" of rows in your source table - dupes on the basis of a unique key on one or more columns.

    Does it matter in which order they are applied to the target table? Think about this for a moment. Assume there's a column called [OrderQuantity] and in one of the rows it has the value 5 and in the other it has the value 3. One of these two rows will be inserted, the other will be subsequently used for an update.

    If you choose the row with [OrderQuantity] = 5 for the INSERT, then the update row will change [OrderQuantity] to 3.

    If you choose the row with [OrderQuantity] = 3 for the INSERT, then the update row will change [OrderQuantity] to 5.

    You have to determine the order in which the two source rows will affect the target table.

    If there can be more than two source rows then you've lost control, because you can only update a target row once in one statement.

    “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