• well this seams to work very well

    "NOTE" that I am matching the records based on "sort of"

    hash made up by concationations of same columns

    from TableA and TableC

    and comparing the resulting values

    MERGE TableA AS ta

    USING (SELECT c.val1 as val1, c.val2 as val2 , c.val3 as val3, c.comID as ComId FROM TableC C) AS tc

    ON

    REPLACE(COALESCE(ta.val1,'')+ COALESCE(ta.val2,'')+COALESCE(ta.val3,''),' ','')

    =

    REPLACE(COALESCE(tc.val1,'')+ COALESCE(tc.val2,'')+COALESCE(tc.val3,''),' ','')

    WHEN NOT MATCHED BY TARGET THEN

    INSERT (val1, val2, val3,entryDate)

    VALUES (tc.val1, tc.val2, tc.val3,@Today)

    OUTPUT INSERTED.ID ,tc.ComId ,1 As RelationshipID

    ,'100.00' As xPercent, 1 As [status],@Today As dateBegin

    INTO TableB (TableAID, comID, RelationshipID, xPercent, [status], dateBegin)

    ;

    [highlight=#ffff11][/highlight][highlight=#ffff11][/highlight]