how to ensure no match when performing insert during MERGE in SQL Server?

Question

I am trying to perform a merge on a table, and am getting a Cannot insert duplicate key row in object error. It says The duplicate key value is (190101011901010100336461440000000002, A)

I have a table that has two keys: [FA Unique Listing Identifier - Ref ID], [Add, Change, Delete Indicator].

[FA Unique Listing Identifier - Ref ID] is the Primary Key and [Add, Change, Delete Indicator] contains 'A', 'C', and 'D' for values. ***Note, this indicator is not for my table. It is considered data for my table with the exception of the value 'D' which means to remove the record from my table.

My rule is simple: if [Add, Change, Delete Indicator] equals 'D', Delete from table, otherwise, Upsert.

Here is my code:

MERGE PROPERTY.DBO.MLS AS T
USING STAGE.DBO.STAGE_MLS AS S
ON (T.[FA Unique Listing Identifier - Ref ID] = S.[FA Unique Listing Identifier - Ref ID])
WHEN MATCHED AND S.[Add, Change, Delete Indicator] <> 'D'
    THEN UPDATE 
    SET T.[Property Address] = S.[Property Address]
,T.[Property City] = S.[Property City]
,T.[Property State] = S.[Property State]
,T.[Property Zip] = S.[Property Zip]
,T.[Property Zip + 4] = S.[Property Zip + 4]
,T.[StreetNum] = S.[StreetNum]
,T.[Direction] = S.[Direction]
,T.[Street_Name] = S.[Street_Name]
,T.[Street_Type] = S.[Street_Type]
,T.[Post_Direction] = S.[Post_Direction]
,T.[Unit] = S.[Unit]
,T.[Street_Name_Post] = S.[Street_Name_Post]
,T.[Assessor's Parcel Identification Number] = S.[Assessor's Parcel Identification Number]
,T.[County FIPS] = S.[County FIPS]
,T.[Internal FA Property Ref ID] = S.[Internal FA Property Ref ID]
,T.[Property Type] = S.[Property Type]
,T.[Property Sub-Type] = S.[Property Sub-Type]
,T.[FA Calculated Days on Market] = S.[FA Calculated Days on Market]
,T.[Current Original Listing Date] = S.[Current Original Listing Date]
,T.[Listing Number] = S.[Listing Number]
,T.[Status] = S.[Status]
,T.[Status Sub Type] = S.[Status Sub Type]
,T.[Rental Indicator] = S.[Rental Indicator]
,T.[Current Listing Price] = S.[Current Listing Price]
,T.[Sold Price] = S.[Sold Price]
,T.[Sold Date] = S.[Sold Date]
,T.[FA Adjusted Sold Price] = S.[FA Adjusted Sold Price]
,T.[FA Sold Price Range] = S.[FA Sold Price Range]
,T.[Listing Agent License Number] = S.[Listing Agent License Number]
,T.[Listing Agent Name] = S.[Listing Agent Name]
,T.[Listing Agent Address] = S.[Listing Agent Address]
,T.[Listing Agent Phone] = S.[Listing Agent Phone]
,T.[Listing Agent Email] = S.[Listing Agent Email]
,T.[Office Name] = S.[Office Name]
,T.[Office Address] = S.[Office Address]
,T.[Office Phone] = S.[Office Phone]
,T.[Office Email] = S.[Office Email]
,T.[Listing Tracking ID] = S.[Listing Tracking ID]
,T.[FA Unique Listing Identifier - Ref ID] = S.[FA Unique Listing Identifier - Ref ID]
,T.[Update Timestamp] = S.[Update Timestamp]
,T.[Agent_SK] = HASHBYTES('MD5', S.[Listing Agent License Number] + '~' + S.[Listing Agent Name]
        + '~' + S.[Listing Agent Address] + '~' + S.[Listing Agent Phone]
        + '~' + S.[Listing Agent Email])
,T.[Add, Change, Delete Indicator] = S.[Add, Change, Delete Indicator]
,T.LoadDate = GETDATE()
WHEN NOT MATCHED AND S.[Add, Change, Delete Indicator]  <> 'D'
    THEN INSERT
            ([Property Address]
,[Property City]
,[Property State]
,[Property Zip]
,[Property Zip + 4]
,[StreetNum]
,[Direction]
,[Street_Name]
,[Street_Type]
,[Post_Direction]
,[Unit]
,[Street_Name_Post]
,[Assessor's Parcel Identification Number]
,[County FIPS]
,[Internal FA Property Ref ID]
,[Property Type]
,[Property Sub-Type]
,[FA Calculated Days on Market]
,[Current Original Listing Date]
,[Listing Number]
,[Status]
,[Status Sub Type]
,[Rental Indicator]
,[Current Listing Price]
,[Sold Price]
,[Sold Date]
,[FA Adjusted Sold Price]
,[FA Sold Price Range]
,[Listing Agent License Number]
,[Listing Agent Name]
,[Listing Agent Address]
,[Listing Agent Phone]
,[Listing Agent Email]
,[Office Name]
,[Office Address]
,[Office Phone]
,[Office Email]
,[Listing Tracking ID]
,[FA Unique Listing Identifier - Ref ID]
,[Update Timestamp]
,[Agent_SK]
,[Add, Change, Delete Indicator]
,LoadDate)
    VALUES
        (S.[Property Address]
,S.[Property City]
,S.[Property State]
,S.[Property Zip]
,S.[Property Zip + 4]
,S.[StreetNum]
,S.[Direction]
,S.[Street_Name]
,S.[Street_Type]
,S.[Post_Direction]
,S.[Unit]
,S.[Street_Name_Post]
,S.[Assessor's Parcel Identification Number]
,S.[County FIPS]
,S.[Internal FA Property Ref ID]
,S.[Property Type]
,S.[Property Sub-Type]
,S.[FA Calculated Days on Market]
,S.[Current Original Listing Date]
,S.[Listing Number]
,S.[Status]
,S.[Status Sub Type]
,S.[Rental Indicator]
,S.[Current Listing Price]
,S.[Sold Price]
,S.[Sold Date]
,S.[FA Adjusted Sold Price]
,S.[FA Sold Price Range]
,S.[Listing Agent License Number]
,S.[Listing Agent Name]
,S.[Listing Agent Address]
,S.[Listing Agent Phone]
,S.[Listing Agent Email]
,S.[Office Name]
,S.[Office Address]
,S.[Office Phone]
,S.[Office Email]
,S.[Listing Tracking ID]
,S.[FA Unique Listing Identifier - Ref ID]
,S.[Update Timestamp]
,HASHBYTES('MD5', S.[Listing Agent License Number] + '~' + S.[Listing Agent Name]
        + '~' + S.[Listing Agent Address] + '~' + S.[Listing Agent Phone]
        + '~' + S.[Listing Agent Email])
,S.[Add, Change, Delete Indicator]
,GETDATE())
WHEN MATCHED AND S.[Add, Change, Delete Indicator] = 'D'
    THEN DELETE;

in this case, if [FA Unique Listing Identifier - Ref ID] matches and [Add, Change, Delete Indicator] is any value but 'D', it is supposed to update, however, it appears that it's trying to insert.

The failure is on the duplicate key. If I am getting the duplicate key failure, shouldn't have the MERGE picked up on the Key and try to do an update first?

Thank you.

Answer 1

Presumably, the "unique" column is not unique in the staging table. Let me venture that you want the last one. If so, you can do:

MERGE PROPERTY.DBO.MLS AS T
    USING (SELECT s.*,
                  ROW_NUMBER() OVER (PARTITION BY [FA Unique Listing Identifier - Ref ID]
                                     ORDER BY ?? DESC
                                    ) as seqnum
           FROM STAGE.DBO.STAGE_MLS AS S
          ) s
    ON T.[FA Unique Listing Identifier - Ref ID] = S.[FA Unique Listing Identifier - Ref ID] AND seqnum = 1
. . .

This will use only the most recent record in the staging table. The ?? is the column used to get the most recent value.

You can also move the comparison to seqnum from the ON to the appropriate logic in the body of the merge.