none
MERGE Statement: What are my options within ON ?

    Question

  • I would like to test the value of Source.MatrixMemberId within ON. If it's value is not matched, however is = to -1, the row is considered incomplete, therefore appropriate for an update.  If  the value is not matched and the value is !=  -1, this is considered complete, new and therefore appropriate for an insert. I realize that what I would like to do and what I can do within ON are 2 different things. What is the  best approach to this problem if left with Multiple WHEN clauses?

         MERGE dimension.Card  AS TARGET
    
    	USING (SELECT DISTINCT [MemberDK]
    				  ,[BuyingUnitDK]
    				  ,[BuyingUnitInternalKey]
    				  ,[MemberInternalKey]
    				  ,[MatrixMemberId]
    				  ,[ClubCardId]
    				  ,[ClubInternalKey]
    	
                FROM [etl].[Card]
    
    ) AS Source
                     ( [MemberDK]
    				  ,[BuyingUnitDK]
    				  ,[BuyingUnitInternalKey]
    				  ,[MemberInternalKey]
    				  ,[MatrixMemberId]
    				  ,[ClubCardId]
    				  ,[ClubInternalKey]
    	
    			  )
    		ON (    Source.ClubCardId				= Target.ClubcardId		
    		    AND Source.MatrixMemberId			= Target.MatrixMemberId				
    			AND Source.ClubInternalKey			= Target.ClubInternalKey
    		   )
    
    
    		WHEN NOT MATCHED THEN
    	    INSERT (   [MemberDK]
    				  ,[BuyingUnitDK]
    				  ,[BuyingUnitInternalKey]
    				  ,[MemberInternalKey]
    				  ,[MatrixMemberId]
    				  ,[ClubCardId]
    				  ,[ClubInternalKey]
    
    	 WHEN MATCHED			
                 THEN UPDATE	
    			 SET MemberDK				=	Source.[MemberDK]
    				,BuyingUnitDK			=	Source.[BuyingUnitDK]
    				,[BuyingUnitInternalKey]=   Source.[BuyingUnitInternalKey]
    				,[MemberInternalKey]    =   Source.[MemberInternalKey]
    				,MatrixMemberId			=	Source.[MatrixMemberId]
    				,ClubCardId				=	Source.[ClubCardId]
    				,ClubInternalKey		=	Source.[ClubInternalKey];



    • Edited by plditallo Wednesday, November 20, 2013 4:30 PM
    Wednesday, November 20, 2013 4:23 PM

Answers

  • I'm not sure I completely understand the dilemma, but you can use compound conditions such as the following:

    WHEN NOT MATCHED BY TARGET AND source.MatrixMemberId <> -1 THEN INSERT...


    Jason

    • Marked as answer by plditallo Wednesday, November 20, 2013 5:35 PM
    Wednesday, November 20, 2013 5:30 PM