title: How implement correctly MERGE for tables? Incorrect CDC result
body: <p>Once of a day one local SQL server doing synchronization with some linked server. The following query using for merge:</p>
  
  <pre><code>MERGE [TargetDb].[dbo].[TargetTable] AS Target
  USING (SELECT * FROM [RemoteServer].[SourceDb].[dbo].[TestTable]) AS Source
      ON (Target.[ID] = Source.[ID])
  WHEN MATCHED THEN
  UPDATE SET  Target.[ID] = Source.[ID],
              Target.[TestFieldString] = Source.[TestFieldString],
              Target.[TestFieldInt] = Source.[TestFieldInt]
  WHEN NOT MATCHED BY TARGET THEN
  INSERT 
      (ID, TestFieldString, TestFieldInt) 
  VALUES 
      (ID, Source.TestFieldString, Source.TestFieldInt);
  </code></pre>
  
  <p>Also on target server works <code>CDC</code> (change data capture). 
  Looks at <code>CDC</code> statistic i found that transaction on update not register in <code>CDC</code>.
  Following query for statistics capture:</p>
  
  <pre><code>SELECT ch.*,  ch.__$seqval,
      CASE ch.__$operation
      WHEN 1 THEN 'delete'
      WHEN 2 THEN 'insert'
      WHEN 3 THEN 'prev val'
      ELSE 'new val' END  as operation,
      map.tran_begin_time, map.tran_end_time
  FROM [cdc].[dbo_TestTable_CT] ch
      JOIN [cdc].[lsn_time_mapping] map
      ON ch.[__$start_lsn] = map.start_lsn
  ORDER BY ch.__$start_lsn, ch.__$seqval
  </code></pre>
  
  <p>After several tests it became clear. This happens with tables that contains the primary key.  If source table with PK contains changes then Merge query doing <code>DELETE</code> and then doing <code>INSERT</code> for each row.</p>
  
  <p>Example of CDC is following:
  <a href="https://i.stack.imgur.com/kQsdr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kQsdr.png" alt="enter image description here"></a></p>
  
  <ol>
  <li>Result of first merge in 1 row. Target table was empty before this operation. </li>
  <li>Update for<code>TestFieldString</code> in source table and result for second merge in 2 and 3 rows. </li>
  <li>Inserting new record in source table and executing merge. Result for third merge in 4,5,6 rows.</li>
  </ol>
  
  <p>It is really strange. I waiting <code>prev val</code> and <code>new val</code> in operation column.
  Script for creating this table looks folowing:</p>
  
  <pre><code>CREATE TABLE [dbo].[TestTable](
      [ID] [uniqueidentifier] NOT NULL PRIMARY KEY,
      [TestFieldString] [varchar](50) NULL,
      [TestFieldInt] [int] NULL
  ) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>Folowing CDC result for similar table without primary key.
  <a href="https://i.stack.imgur.com/XDGAs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XDGAs.png" alt="enter image description here"></a></p>
  
  <ol>
  <li>Result of first merge in 1 row. Target table was empty before this operation. </li>
  <li>Updating for<code>TestFieldString</code> in source table and merging. Result in 2 and 3 rows. </li>
  <li>Inserting new record in source table and merging. Result in 4 row.</li>
  </ol>
  
  <p>It is correct <code>CDC</code> result for following table:</p>
  
  <pre><code>CREATE TABLE [dbo].[TestTable](
      [ID] [uniqueidentifier],
      [TestFieldString] [varchar](50) NULL,
      [TestFieldInt] [int] NULL
  ) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>I read on MSD about MERGE but don't find description for this issue.</p>
  
  <p>How fix it and what is wrong? Maybe it is bug or feature of <code>MERGE</code> clause or <code>CDC</code> functionality? It is a specifically issue, but for the functionality that I'm developing, it is a very critical issue.</p>
  
  <p>Also, before merge I was attempted to disabled constraints in table but got an error that say CDC was locked the constraints. 
  Maybe there is possible mark the constraints (in my case it is primary key) before merge and after this get correct CDC result?</p>
  
  <p>Also, before merge runing folowing SP:</p>
  
  <pre><code>exec sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'
  </code></pre>
  
  <p>And after merge folowing:</p>
  
  <pre><code>exec sp_MSforeachtable @command1='ALTER TABLE ? CHECK CONSTRAINT ALL'
  </code></pre>