Here is an attempt to use a CTE and I am lost again, not sure if this is going to work as I expected. Code below throws the same merge error as before.

I changed the group by columns and the on "target" columns around a few times and still I am not getting this to work...

Maybe its something simple I am missing?

ALTER PROCEDURE [dbo].[sp_Merge_AssetClassUDF_V2]

@PullDate Date

AS BEGIN

MERGE [AssetClassUDF] AS target

USING (

SELECT ASSET_UDF_VALUE, ASSET_UDF_DESC, ASSETDETAIL_Id

FROM AssetClassUDF

GROUP BY ASSET_UDF_DESC,ASSET_UDF_VALUE)

AS AssetClassUDF_CTE (ASSET_UDF_VALUE, ASSET_UDF_DESC, ASSETDETAIL_Id)

ON

(target.ASSETDETAIL_Id = AssetClassUDF_CTE.ASSETDETAIL_Id

AND target.ASSET_UDF_DESC = AssetClassUDF_CTE.ASSET_UDF_DESC)

WHEN MATCHED THEN

UPDATE SET

[ASSET_UDF_VALUE] = AssetClassUDF_CTE.[ASSET_UDF_VALUE],

[ASSET_UDF_DESC] = AssetClassUDF_CTE.[ASSET_UDF_DESC],

[ASSETDETAIL_Id] = AssetClassUDF_CTE.[ASSETDETAIL_Id],

Pulldate = @PullDate

WHEN NOT MATCHED THEN

INSERT (

[ASSET_UDF_VALUE],

[ASSET_UDF_DESC],

[ASSETDETAIL_Id],

Pulldate)

VALUES (

AssetClassUDF_CTE.[ASSET_UDF_VALUE],

AssetClassUDF_CTE.[ASSET_UDF_DESC],

AssetClassUDF_CTE.[ASSETDETAIL_Id],

@PullDate);

END

Would I be better to run separate update and inserts and use a CTE to delete duplicates? Basically avoiding Merge's logic altogether?

At the end of the day I need to update the asset_udf_value when it changes for an existing asset_id (update) and then insert new rows when new data is added from the feed. (simple insert)