Procedure showing an error related to merge in sql and please verify that overall code is fine or not

Question

Code is showing an error

Merge statements with a WHEN NOT MATCHED [BY TARGET] clause must target a hash distributed table

Also please verify that the overall syntax is fine or not?

MERGE IR_CREDITREQUEST_SPTESTING AS T
USING (
    SELECT 
             TT.TaxYear
            ,TT.TaxPayerSSN          
            ,TT.EngagementID    
            ,TT.CreditRequestType   
            ,TT.BorrowerID  
            ,TT.NameSuffix  
            ,TT.FirstName   
            ,TT.MiddleName  
            ,TT.LastName    
            ,TT.SpouseName  
            ,TT.SpouseSSN   
            ,TT.PrintPositionType   
            ,TT.MaritalStatusType   
            ,TT.StreetAddress   
            ,TT.City    
            ,TT.State   
            ,TT.PostalCode  
            ,TT.Type    
            ,TT.Value   
            ,TT.RequestDateTime 
            ,TT.CreatedDateTime 
            ,TT.RequestProcessed    
            ,TT.BorrowerResidencyType   
            ,TT.isActive                    
     FROM 
           IR_CREDITREQUEST_SPTESTING TT
     INNER JOIN (
         select DISTINCT
            A.TaxYear,
            A.[TaxPayer-SSN] AS TaxPayerSSN,
            A.EngagementID AS EngagementID,
            CASE
              WHEN A.[Filing Status] = 'MFJ' THEN 'Joint'
              ELSE A.[Filing Status]
              END AS [CreditRequestType],
            '' AS BorrowerID ,
            A.[TaxPayer-Title/Suffix] AS NameSuffix,
            A.[TaxPayer-FirstName] AS FirstName,
            '' AS MiddleName,
            A.[TaxPayer-LastName] AS LastName,
            B.[Spouse-FirstName] + ' ' + B.[Spouse-LastName] AS [SpouseName],
            B.[Spouse-SSN] AS [SpouseSSN],
            CASE
              WHEN A.[Filing Status] = 'MFJ' THEN 'CoBorrower'
              WHEN A.[Filing Status] = 'Single' THEN 'Borrower'
              ELSE A.[Filing Status]
              END AS [PrintPositionType],
            CASE
              WHEN B.[Spouse-FirstName] IS not null THEN 'Married'
              ELSE 'Unmarried'
              END AS [MaritalStatusType],
            C.[Address] AS StreetAddress,
            C.City AS City,
            C.[State] AS State,
            C.[Postal Code] AS PostalCode,
            A.[Primary Contact] AS Type,
            A.[TaxPayer-Home/Evening Telephone Number] AS Value,
            NULL AS RequestDateTime,
            GETDATE() AS CreatedDateTime,
            NULL AS RequestProcessed,
            NULL AS BorrowerResidencyType,
            0 AS IsActive 
        from DimTaxPayerInfo A
        LEFT join DimTaxPayerSpouseInfo B on A.[TaxPayer-SSN] = B.[TaxPayer-SSN] AND B.TaxSoftwareId = 4
        LEFT join stg.stg_DimTaxPayerAddress C on A.[TaxPayer-SSN] = C.[TaxPayer-SSN]
        WHERE A.[TaxPayer-SSN] != ''
    ) as Y 
    ON TT.[EngagementID] = Y.[EngagementID]
) DD
ON T.[EngagementID]  = DD.[EngagementID]                            
WHEN MATCHED THEN
    Update
                             SET T.TaxYear      =  DD.TaxYear
                                ,T.TaxPayerSSN      =  dd.TaxPayerSSN              
                                ,T.EngagementID     =  dd.EngagementID          
                                ,T.CreditRequestType = dd.CreditRequestType 
                                ,T.BorrowerID       =  dd.BorrowerID            
                                ,T.NameSuffix       =  dd.NameSuffix            
                                ,T.FirstName        =  dd.FirstName         
                                ,T.MiddleName       =  dd.MiddleName            
                                ,T.LastName         =  dd.LastName              
                                ,T.SpouseName       =  dd.SpouseName            
                                ,T.SpouseSSN        =  dd.SpouseSSN         
                                ,T.PrintPositionType = dd.PrintPositionType 
                                ,T.MaritalStatusType = dd.MaritalStatusType 
                                ,T.StreetAddress    =  dd.StreetAddress     
                                ,T.City             =  dd.City                  
                                ,T.State            =  dd.State             
                                ,T.PostalCode       =  dd.PostalCode            
                                ,T.Type             =  dd.Type                  
                                ,T.Value            =  dd.Value             
                                ,T.RequestDateTime  =  dd.RequestDateTime       
                                ,T.CreatedDateTime  =  dd.CreatedDateTime       
                                ,T.RequestProcessed =  dd.RequestProcessed      
                                ,T.BorrowerResidencyType = dd.BorrowerResidencyType
                                ,T.IsActive = dd.IsActive 

                                WHEN NOT MATCHED  THEN
                                INSERT
                                (    TaxYear
                                    ,TaxPayerSSN             
                                    ,EngagementID   
                                    ,CreditRequestType  
                                    ,BorrowerID 
                                    ,NameSuffix 
                                    ,FirstName  
                                    ,MiddleName 
                                    ,LastName   
                                    ,SpouseName 
                                    ,SpouseSSN  
                                    ,PrintPositionType  
                                    ,MaritalStatusType  
                                    ,StreetAddress  
                                    ,City   
                                    ,State  
                                    ,PostalCode 
                                    ,Type   
                                    ,Value  
                                    ,RequestDateTime    
                                    ,CreatedDateTime    
                                    ,RequestProcessed   
                                    ,BorrowerResidencyType  
                                    ,IsActive
                                    )           
                                values (
                                     dd.TaxYear
                                    ,dd.TaxPayerSSN          
                                    ,dd.EngagementID    
                                    ,dd.CreditRequestType   
                                    ,dd.BorrowerID  
                                    ,dd.NameSuffix  
                                    ,dd.FirstName   
                                    ,dd.MiddleName  
                                    ,dd.LastName    
                                    ,dd.SpouseName  
                                    ,dd.SpouseSSN   
                                    ,dd.PrintPositionType   
                                    ,dd.MaritalStatusType   
                                    ,dd.StreetAddress   
                                    ,dd.City    
                                    ,dd.State   
                                    ,dd.PostalCode  
                                    ,dd.Type    
                                    ,dd.Value   
                                    ,dd.RequestDateTime 
                                    ,dd.CreatedDateTime 
                                    ,dd.RequestProcessed    
                                    ,dd.BorrowerResidencyType   
                                    ,dd.IsActive
                                    );

You may want to reformat that so it makes sense. The WHEN NOT part should be on the top level (totally left), you know. It is obvious, looking at the formatting, that this is as complex as it gets, which is not what we do here - present a minimum example. — TomTom, Nov 04 '21 at 15:40
As one hint - you ahv a nice FROM USING on the top, then the formatting has the WHEN MATCHED furthe rin. YOu do realize that this is ONLY logical when you DO NOT CLOSE THE ( FROM THE USING? Simple type? — TomTom, Nov 04 '21 at 15:42
Merge statements with a WHEN NOT MATCHED [BY TARGET] clause must target a hash distributed table. — Shah Rukh Ali, Nov 04 '21 at 15:42
In regards to *"Also please verify that the overall syntax is fine or not?"* if the syntax wasn't "fine" you would be getting a syntax error. — Thom A, Nov 04 '21 at 15:42
I appreciate that the `Merge` statement looks cool, you can do a lot in one statement but its not necessarily the best way to do such operations, I would suggest breaking down your Merge in to old traditional UPSERT operation by Updating existing, Inserting new etc etc. Also there are some really good reasons to avoid Merge statement all together take a look at this article ['Use Caution with SQL Server's MERGE Statement'](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) — M.Ali, Nov 04 '21 at 15:43
If the error says you "must target a hash distributed table" then you must. What is your difficulty, do you have a hash distributed table? — Charlieface, Nov 04 '21 at 21:09

score 0 · Answer 1 · answered Dec 02 '21 at 08:27

0

Your Merge Statement looks correct. This error is mostly seen in Azure Synapse. Make sure your target table is hash distributed to avoid this error.

Refer to the answer posted in the thread for a similar error.

answered Dec 02 '21 at 08:27

NiharikaMoola-MT

4,700
1
3
15

Procedure showing an error related to merge in sql and please verify that overall code is fine or not

1 Answers1