I would say look out for nulls.
Also, if your result set has millions of rows, do temp tables and forget about table variables.
Stay away from this unless you know you will only have one row.
DECLARE @CampaignsLeads TABLE ([yourfieldshere] varchar(100))
INSERT INTO @CampaignsLeads
-- Finally Join @CampaignsEvents with @CampaignsLeads
this will make your server do joins between millions and million of rows using one thread so your CPU will be low but the query will run worse than ever before because in 2008 the statistics is hard coded as 1 row so you will have loop join hell occurring between millions of rows.
Seriously, forget about table variables when dealing with more than one row.
Forever and ever till kingdom come.
select distinct *,
position AS POSITION_DERIVED,
CAMPAIGN_PERSON_STATUS AS PERSON_STATUS_DERIVED,
CAMPAIGN_COMPANY_NAME AS COMPANY_NAME_DERIVED,
CAMPAIGN_FIRST_NAME + '' + CAMPAIGN_LAST_NAME AS FULL_NAME_DERIVED
into #all_date_joined
from CAMPAIGNS c (nolock)
full outer join EVENTS a (nolock)
on c.campaign_mpam_activity_id=a.mpam_activity_id
and (a.semr_uuid=c.CAMPAIGN_UUID
or (a.SEMR_EMAIL_ADDRESS=c.CAMPAIGN_EMAIL_ADDRESS
and A.SEMR_EMAIL_ADDRESS<>'idevents'
)
or a.SEMR_CONTACT_ID=C.CAMPAIGN_CONTACT_ID
OR (A.SEMR_FIRST_NAME=C.CAMPAIGN_FIRST_NAME
AND A.SEMR_LAST_NAME=C.CAMPAIGN_LAST_NAME
AND A.SEMR_COMPANY_NAME=C.CAMPAIGN_COMPANY_NAME
)
)
full outer join LEADS b (nolock)
on a.MPAM_BGET_ID=b.PLDS_BGET_ID
and (a.SEMR_CONTACT_ID=b.CONTACT_ID
or (a.SEMR_EMAIL_ADDRESS=b.PLDS_EMAIL_ADDRESS
and a.SEMR_EMAIL_ADDRESS<>'idevents')
or
(a.SEMR_FIRST_NAME=B.PLDS_FIRST_NAME
and a.SEMR_LAST_NAME=B.PLDS_LAST_NAME
and A.SEMR_COMPANY_NAME=B.PLDS_COMPANY_NAME
)
)