I have data flowing into one table from multiple other tables lets say: Table_A Then I have a Merge stored proc that takes data from table A merges it with Table B.
However, something doesn't seem to be right. If i truncate and load the data it works fine, but if i dont truncate and load, and just fetch the query by eachh hour I get the error message saying
Msg 8672, Level 16, State 1, Procedure Merge_Table_A, Line 4 [Batch Start Line 0] The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
How can I overcome this?
I want to be able to incrementally load the data and not do truncate loads, but at the same time have a stored proc that updates or inserts or doesnt care if the row already exists.