SQL-Squid (11/7/2012)

---

Tava (11/6/2012)

---

SQL-Squid,

You're correct in what you said the issue is that your source has duplicates, so based on the below you will always return multiple results, hence the MERGE statement returns that error.

MERGE [AssetClassUDF] AS target

USING [RawData_AssetClassUDF] AS source

ON target.ASSETDETAIL_Id = source.ASSETDETAIL_Id

AND target.ASSET_UDF_DESC = source.ASSET_UDF_DESC

Unfortunatley with the MERGE as the error says you cant update/insert the same record twice... so you only have 2 options.

1. use a GROUP BY in your MERGE so that way it will treat duplicates as One value & this should fix your problem (If this is what you want)

or

2. Clean the SOURCE so it doesn't contain duplicates.

I personally don't like Option 2 - Cleaning the source file as this is how it gets delivered and always good reference point when you want to compare RAW -> STAGING -> LIVE.

Tava,

Please give an example (if you have time) of how to use the group by in the merge. I am been trying to figure this out and I am still lost.

Right now, I am running an update then insert and a cte to remove dups. Its messy and I don't feel its the best way to handle this. I also have a trigger on delete that archives these updates to an archive table to have a full history. With the update & insert routine, I am now having to run the delete dups cte against the archive table. I'm not getting the warm fuzzy feeling this is a correct way to implement all this.

I was told I can request the app designer to include a primary key in the feed I receive, if that does happen, the merge will be a lot better, but if not I need to have something in place to work.