title: How do these SQL-Server-2005 MERGE alternatives compare
body: <p>In SQL-Server-2005 how are the following approaches likely to compare in terms of performance for a bulk update of data. </p>
  
  <p><strong>Bulk Delete then Bulk Insert</strong></p>
  
  <pre><code>--Delete Existing Data
  DELETE FROM Foo
  WHERE Category = 1;
  
  --Insert New Data
  INSERT INTO Foo (Id, Category, ...)
  SELECT Id, Category, ...
  FROM @Temp;
  </code></pre>
  
  <p><strong>(Merge Style) Bulk Insert/Update/Delete</strong></p>
  
  <pre><code>--Delete Existing Data Not In New Data
  DELETE FROM Foo 
  FROM Foo LEFT OUTER JOIN @Temp T ON Foo.Id = T.Id
  WHERE Foo.Category = 1
  AND T.Id IS NULL;
  
  --Update Matching Data
  UPDATE Foo
  SET
      Foo.Category = T.Category,
  ...
  FROM Foo INNER JOIN @Temp T ON Foo.Id = T.Id;
  
  --Insert New Data
  INSERT INTO Foo (Id, Category, ...)
  SELECT Id, Category, ...
  FROM @Temp T LEFT OUTER JOIN Foo F ON F.Id = T.Id
  WHERE F.Id IS NULL;
  </code></pre>