Example Stage (day 2): Merge Script

MERGE INTO lST_Stage AS st
USING Stages_Raw_day2 AS src
ON
    -- this should be the natural key
    src.[address] = st.ST_LOC_Stage_Location
-- if the key was known then logically update (actually insert) possibly changed valued
WHEN MATCHED THEN UPDATE
SET
    st.Metadata_ST = 42,
    st.ST_NAM_Stage_Name = isnull(src.[name], st.ST_NAM_Stage_Name)
-- if the key was unknown then insert the new values (an identity will be automatically created)
WHEN NOT MATCHED THEN INSERT (
    Metadata_ST,
    ST_NAM_Stage_Name,
    ST_LOC_Stage_Location
)
VALUES (
    42,
    src.[name], -- works even if null
    src.[address]
);