Loading data to temp table from staging table

Solution 1:

the final merge is missing a few things.

• the INTO after MERGE
• I also fully qualified the table name (maybe not needed)
• you use the alias PDTC & PDSC it they are TC & SC
• The N being inserted in RCD_CLOSE_FLG, is a char, so it needs to be wrapped in single quotes 'N'

MERGE INTO PRASHANT_DWH.TMP.PRASHANT_DWH_TMP_COUNTRY AS TC
USING PRASHANT_DWH.STG.PRASHANT_DWH_STG_COUNTRY AS SC
    ON TC.ID = SC.ID 
WHEN MATCHED THEN 
    UPDATE TC.ID_SUR_KEY = SC.ID + 1
    INSERT (ID, COUNTRY_DESC, RCD_INS_TS, RCD_UPD_TS, RCD_START_DI, RCD_CLOSE_DI, RCD_CLOSE_FLG, ID_SUR_KEY) 
    VALUES (SC.ID, SC.COUNTRY_DESC, current_timestamp, current_timestamp, current_date, current_date, 'N', SC.ID + 1)
WHEN NOT MATCHEN BY TARGET THEN DELETE

But the tail of your MERGE SQL is a bit of a mess, and I am not sure how to fix it, as what you are trying to do is not clear to me.

The INSERT should be bound to a WHEN MATCHED THEN or WHEN NOT MATCHED THEN and your WHEN NOT MATCHEN BY TARGET THEN DELETE seems..

It ether should be WHEN NOT MATCHEN THEN DELETE, but that seems to interfere with the INSERT.

It makes the most sense to UPDATE & Insert, and ignore the DELETE path,

MERGE INTO PRASHANT_DWH.TMP.PRASHANT_DWH_TMP_COUNTRY AS TC
USING PRASHANT_DWH.STG.PRASHANT_DWH_STG_COUNTRY AS SC
    ON TC.ID = SC.ID 
WHEN MATCHED THEN 
    UPDATE TC.ID_SUR_KEY = SC.ID + 1
WHEN NOT MATCHED THEN
    INSERT (ID, COUNTRY_DESC, RCD_INS_TS, RCD_UPD_TS, 
        RCD_START_DI, RCD_CLOSE_DI, RCD_CLOSE_FLG, ID_SUR_KEY) 
    VALUES (SC.ID, SC.COUNTRY_DESC, current_timestamp, 
        current_timestamp, current_date, current_date, 'N', 
        SC.ID + 1);