I totally agree with that, the use of the merge command is for me the better option. I designed several ETL packages the last year and I tried the 3 options, and at the end I always used the Merge command.
Here I leave you a code snippet from my personal library. This code makes the following:
- Log the start time and name of the package in a custom log table with an identity column
- Merge into a target table the results of a query (as a source). You can adapt the source query to your needs
- The columns that match the merge condition are updated, if not, they are inserted.
- At the end the finish time is log together with the row count of updated and inserted records.
-- I use a table to log the start and finish time, package name, number of rows inserted and number of rows updated
insert into SSIS_Log
(?,GetDate()); -- Map the System variable "PackageName"
@mergeResultsTable table (MergeAction VARCHAR(20));
MERGE Table_A AS TARGET
) AS SOURCE
ON (TARGET.Key1 = SOURCE.Key1 and TARGET.Key_2 = SOURCE.Key_2)
WHEN MATCHED THEN UPDATE SET
,TARGET.UpdateDT=GetDate() -- I create a UpdateDT column in the target table to store the update datetime
WHEN NOT MATCHED BY TARGET THEN INSERT
,CreateDT -- I create a CreateDT column in the target table to store the insert (or creation) datetime
OUTPUT $action into @mergeResultsTable;
set UpdatedCount = -- Log the count of updated rows
WHERE MergeAction = 'UPDATE')
, InsertedCount = -- Log the count of inserted rows
WHERE MergeAction = 'INSERT')
,DTFinish = GetDate()
where Idx =
If your target table should be treated as a SCD I strongly recommend to you the following article by Adam Aspin:Slowly changing dimensions using T-SQL MERGE
If you have further question just write me.