I'm testing out CDC (Capture Data Changes) on MSSQL 2008 with the MERGE command. When records are matched I run an update statement, but in the CDC change table the Update is a series of [Delete and Insert] operations.
I have confirmed that MERGE is running the Update portion, just not sure why that translates to Delete and Insert for each updated record...
IF you have 300 identical records (source and destination) and merge "updates" them on match. You will have 600 records in CDC
Update:
The Update part of my merge statement was run... Any match triggered full table update. Also, I was thrown off because a merge does a DELETE, then INSERT, instead of an Update. At least that's what CDC records