I have recently spent some time using SSIS to import CRM data into a friendly SQL relational table structure. Part of the SSIS package uses a stored procedure to synchronize a target table with updates/inserts/deletes from a staging table. To this end I employed a SQL MERGE statement. The MERGE statement is a really powerful way to create what’s called “upserts”. My first attempt at the MERGE statement worked but I was not 100% happy. The reason for this was the UPDATE part. I found that it was updating every single record in the target table even if there had been no changes in the staging table.
WHEN MATCHED THEN UPDATE SET.....
So how can one limit the scope of MERGE operations? When working with large result sets, particularly with incremental updates, most of the matched rows will probably already be in sync. The answer is to add additional condition within the MATCHED clause.
WHEN MATCHED AND (TRGT.modifiedon <> SRCE.modifiedon) THEN UPDATE SET
This can provide a huge performance boost.
Mango IT 