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]
);