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
4条答案
按热度按时间uz75evzq1#
If you exclude the columns that are primary keys in the UPDATE SET operation of the Merge on your target table, on which the cdc is enabled, it will correctly record the update in your capture instance table.
gpfsuwkq2#
The MERGE command can do 'legit' updates, you're seeing 600 rows in the CDC data for 300 updates because CDC tracks the before and after for each individual row affected. Check out the 'Change Table' section of this article in Books Online.
Each insert or delete operation that is applied to a source table appears as a single row within the change table. The data columns of the row that results from an insert operation contain the column values after the insert. The data columns of the row that results from a delete operation contain the column values before the delete. An update operation requires one row entry to identify the column values before the update, and a second row entry to identify the column values after the update.
7hiiyaii3#
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
rkue9o1l4#
It could also be caused by a unique index on some columns that are updated