title: Speed up MERGE in SQL Server on millions of rows
body: <p>I'm trying to insert/update millions of rows into a table that already contains millions of rows.</p>
  <p>The target table contains an ID column (PK) and a number of other columns.</p>
  <p>I'm inserting the data from a csv into a temporary table #temp (same columns as the target table except for ID).</p>
  <p>Then I try to merge them, but the merge is insanely slow (multiple hours for 10M rows).</p>
  <pre><code>-- Create temporal table #temp with almost all the columns in target_table
  SELECT ColumnA,
          ColumnB,
          ColumnC,
          ColumnD,
          ColumnE,
          ColumnF,
          ColumnG,
          ColumnH
  INTO #temp
  FROM target_table
  WHERE 1 = 2;
  
  &lt;HERE I DO A BULK INSERT INTO #temp WHICH ONLY TAKES A FEW SECONDS&gt;
  
  -- -- Merge into target_table
  MERGE target_table AS TARGET
  USING #temp AS SOURCE
  ON (
              TARGET.ColumnA = SOURCE.ColumnA
          AND
              TARGET.ColumnB = SOURCE.ColumnB
          AND
              TARGET.ColumnC = SOURCE.ColumnC
          AND
              TARGET.ColumnE = SOURCE.ColumnE
      )
      
  WHEN MATCHED
      AND (
              (TARGET.ColumnG IS NULL AND SOURCE.ColumnG IS NOT NULL OR TARGET.ColumnG &lt;&gt; SOURCE.ColumnG)
              OR
              (TARGET.ColumnH IS NULL AND SOURCE.ColumnH IS NOT NULL OR TARGET.ColumnH &lt;&gt; SOURCE.ColumnH)
          )
      THEN
      UPDATE SET TARGET.ColumnG = SOURCE.ColumnG, TARGET.ColumnH = SOURCE.ColumnH
  
  WHEN NOT MATCHED BY TARGET
      THEN
      INSERT (ColumnA, ColumnB, ColumnC, ColumnD, ColumnE, ColumnF, ColumnG, ColumnH)
      VALUES (SOURCE.ColumnA, SOURCE.ColumnB, SOURCE.ColumnC, SOURCE.ColumnD, SOURCE.ColumnE, SOURCE.ColumnF,
              SOURCE.ColumnG, SOURCE.ColumnH);
  </code></pre>
  <p>After the bulk insert, the execution plan for the MERGE has some very expensive Sort's (41% of the total cost).</p>
  <p>Any recommendations on how to speed up the merge?</p>
  <p>Thanks!</p>