It's not going to make much difference to the performance but and SS.QTY_RECEIVED is not null is not required because
and SS.QTY_RECEIVED <> 0 will...

from qnxt.dbo.memberpcp mp
join qnxt.dbo.affiliation a1 (nolock) on mp.affiliationid = a1.affiliationid
join qnxt.dbo.affiliation a2 (nolock) on mp.paytoaffilid = a2.affiliationid
join qnxt.dbo.provider p1 (nolock) on a1.provid = p1.provid
join...

Have you tried FULL JOIN instead of INNER?

All our scripts have a CREATE that's executed if the sp doesn't exist followed by an ALTER. That way all the security permissions are preserved. We also sometimes have comments...

SSIS can create its own system logging table
select * from dbo.sysssislog

Another reason why you should always prefix the column names with the table name (or alias) .

You will get better performance from an UPDATE followed by an INSERT than you'll get from a MERGE.

Your primary key constraint name is the same as your table name.
Change it to: CONSTRAINT PK_CSize PRIMARY KEY (CID),

Sounds like you want some logic like this:
SELECT IIF(c.column1 IS NULL AND c.column2 IS NULL,d.column3,c.column1) col1,
       IIF(c.column1 IS NULL AND c.column2 IS...