I recently had to do an UPSERT. (Yes, it’s a real word: UPdate + inSERT.) This would normally be an UPDATE followed by in INSERT wrapped in a transaction so if either fails it all rolls back. This means you will make at least two passes through the data, locking it until the transaction commits. Logically this is a single operation, wouldn’t it be nice to have a single construct that could handle the UPDATE, the INSERT, and maybe even a DELETE if necessary? That’s exactly what we have in the MERGE statement. SQL Books Online gives a good explanation of the syntax and a few examples but it can get very complex very quickly.

USE tempdb
GO
    IF (OBJECT_ID('dbo.#Target')) IS NOT NULL 
        DROP TABLE #Target; 
        
    IF (OBJECT_ID('dbo.#Source')) IS NOT NULL 
        DROP TABLE #Source;        

    --Dupsert this table...
        CREATE TABLE #Target 
            (
                TargetID    INTEGER    IDENTITY PRIMARY KEY NOT NULL
                ,DataCol    CHAR(10)    NOT NULL
            );
        INSERT INTO #Target (DataCol) 
            VALUES ('Static'),('Update Me')
            ,('Delete Me')
            ;
        
    --...with changes from this one.
        CREATE TABLE #Source 
            (
                SourceID    INTEGER    IDENTITY(101,1) PRIMARY KEY NOT NULL
                ,TargetID    INTEGER    NULL 
                ,DataCol    CHAR(10)    NOT NULL
            );
        INSERT INTO #Source (DataCol,TargetID) 
            VALUES ('Static',1),('Change',2),('New',NULL);
        
    --View tables    
        SELECT * FROM #Target;
        SELECT * FROM #Source;

    --Dupsert the target using MERGE
        MERGE 
            INTO #Target 
            USING #Source
                ON    #Target.TargetID = #Source.TargetID
            WHEN MATCHED THEN UPDATE 
                SET #Target.DataCol = #Source.DataCol
            WHEN NOT MATCHED BY TARGET THEN 
                INSERT (DataCol)
                VALUES (#Source.DataCol)
            WHEN NOT MATCHED BY SOURCE THEN 
                DELETE;

    --View target    
        SELECT * FROM #Target;

As you can see, the code is a logical unit, straightforward, and flexible.  Enjoy!