SQL Insert Update using MERGE

This will be a very short blog post to help you understand the syntax and usage of Merge SQL statement. I will use same program table ([STG].[TMS_TOP_PROGRAMS]) as source table which I am used in my some of of the other blog posts.

 

MERGE is very handy when it comes to update or insert using a single SQL statement. In my example I will use PROGRAM_ID to check whether row already exists and if so will update all other attributes else will insert into [STG].[TMS_TOP_PROGRAMS] table.

 

This is the source table ([STG].[TMS_TOP_PROGRAMS]).

 

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
11	Align the Right Customers to the Right Solutions at Optimized cost - New	1	100
12	Create competitive advantage through business analytics and benchmarking - New	6	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement - New	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities - New	4	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100
7	Enterprise Services	8	100

This is how destination table ([DW].[TMS_DW_TOP_PROGRAMS]). Idea is, key from the source table (PROGRAM_ID in my example) will be compared against destination table (PROGRAM_ID column) and if match found a particulate row will be updated else new row will be inserted into the table.

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
1	Organization Alignment	7	100
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
12	Create competitive advantage through business analytics and benchmarking	6	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100

Let’s write and check query now. Following query will insert Program ID 7 into destination table as it does not exist and will update all other records from the source. Note that Program ID 1 in the destination table will not have any impact as it is not present in the source.

MERGE [DW].[TMS_DW_TOP_PROGRAMS] AS SR
USING
(
SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, SORT_ORDER, [BATCH_ID], RANK_ID FROM
(
    SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER, [BATCH_ID],
    ROW_NUMBER() OVER (
      PARTITION BY PROGRAM_ID ORDER BY PROGRAM_ID) AS RANK_ID
  FROM [STG].[TMS_TOP_PROGRAMS]
) AS INTBL WHERE RANK_ID = 1
) AS TR
ON SR.PROGRAM_ID = TR.PROGRAM_ID
WHEN MATCHED THEN 
    UPDATE SET SR.[PROGRAM_NAME] = TR.[PROGRAM_NAME], 
            SR.KEY_PROGRAM = TR.KEY_PROGRAM, 
            SR.SORT_ORDER = TR.SORT_ORDER, 
WHEN NOT MATCHED THEN 
    INSERT (
            PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM,      
            PROGRAM_SORT_ORDER, [BATCH_ID]
            )
    VALUES (
            TR.PROGRAM_ID, TR.[PROGRAM_NAME], TR.KEY_PROGRAM,  
            TR.PROGRAM_SORT_ORDER, TR.[BATCH_ID]
           );