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]