Got it, let's break down what's happening here and how to fix it. First off, that error message tells us a target row is matching multiple source rows—while you suspect the * in your pasa_cd values is being treated as a wildcard, it's important to note that SQL Server's = operator does exact matches by default (wildcards only work with LIKE). That said, let's cover both your hypothesis and the actual root cause of the duplicate match.

1. Lock Down Exact Matches & Guard Against Source Duplicates #

Even if you're sure there are no duplicates, adding a grouping step to your source query will ensure each match key (the combination of corg_id and corg_active_ind) is unique. This directly addresses the error's requirement that a target row matches at most one source row. Here's the adjusted MERGE statement:

MERGE INTO TEMSPASA 
USING (
    -- Group source data to eliminate duplicate match keys
    SELECT 
        LTRIM(RTRIM(corg_id)) AS corg_id,
        LTRIM(RTRIM(corg_nm)) AS corg_nm,
        corg_active_ind
    FROM OPENQUERY(orad, 'SELECT * FROM CDAS.TDWHCORG')
    GROUP BY LTRIM(RTRIM(corg_id)), LTRIM(RTRIM(corg_nm)), corg_active_ind
) AS TDWHPASA 
ON TEMSPASA.pasa_cd = TDWHPASA.corg_id 
AND TEMSPASA.pasa_active_ind = TDWHPASA.corg_active_ind 
WHEN MATCHED THEN 
UPDATE SET 
    TEMSPASA.pasa_desc = TDWHPASA.corg_nm, 
    TEMSPASA.pasa_active_ind = TDWHPASA.corg_active_ind 
WHEN NOT MATCHED THEN 
INSERT (pasa_cd, pasa_desc, pasa_active_ind) 
VALUES (TDWHPASA.corg_id, TDWHPASA.corg_nm, TDWHPASA.corg_active_ind);

I also moved the LTRIM(RTRIM) to the source subquery so we only clean those values once, which is more efficient.

2. Diagnose the Actual Duplicate Matches #

To confirm what's causing the multiple matches, run this diagnostic query. It will show you which source keys are duplicated, or which source keys are matching multiple target rows:

SELECT 
    LTRIM(RTRIM(TDWHPASA.corg_id)) AS clean_corg_id,
    TDWHPASA.corg_active_ind,
    COUNT(*) AS source_row_count,
    -- Count how many target rows this source key matches
    (SELECT COUNT(*) 
     FROM TEMSPASA tp 
     WHERE tp.pasa_cd = LTRIM(RTRIM(TDWHPASA.corg_id)) 
       AND tp.pasa_active_ind = TDWHPASA.corg_active_ind) AS target_match_count
FROM OPENQUERY(orad, 'SELECT * FROM CDAS.TDWHCORG') AS TDWHPASA
GROUP BY LTRIM(RTRIM(TDWHPASA.corg_id)), TDWHPASA.corg_active_ind
HAVING COUNT(*) > 1 OR target_match_count > 1;

This will pinpoint exactly where the overlap is—chances are you'll find a source key that's repeated in the Oracle data, or a source key that matches both H04 and H04* in your target table (though the exact = match shouldn't do that, so this query will clarify).

3. If You Do Need to Handle Literal * with Pattern Matching #

If for some reason you're using LIKE in your ON clause (even though your current query uses =), you can escape the * to treat it as a literal character. Here's how you'd adjust the ON clause for that scenario:

-- Only use this if you're intentionally using LIKE for pattern matching!
ON TEMSPASA.pasa_cd LIKE REPLACE(TDWHPASA.corg_id, '*', '\*') ESCAPE '\'
AND TEMSPASA.pasa_active_ind = TDWHPASA.corg_active_ind

But again, your original query uses = which doesn't treat * as a wildcard, so this is a backup only if you switch to pattern matching later.

内容的提问来源于stack exchange，提问作者dk96m