USP_CMS_MIGRATEANONYMOUSEMAILPREFERENCES

CREATE procedure dbo.USP_CMS_MIGRATEANONYMOUSEMAILPREFERENCES
(
    @NUMRECORDSPROCESSED int OUTPUT
)
as
begin

    declare @CHANGEAGENTID uniqueidentifier = null
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    --this temp table is to store the ids that we want to migrate

    declare @FOUNDSUBSCRIBERS table(ANONYMOUSEMAILSUBSCRIBERID uniqueidentifier, CONSTITUENTID uniqueidentifier, EMAILADDRESS nvarchar(200), EMAILADDRESSID uniqueidentifier)

    --prepare records

    insert into @FOUNDSUBSCRIBERS (ANONYMOUSEMAILSUBSCRIBERID, CONSTITUENTID, EMAILADDRESS, EMAILADDRESSID) 
    select distinct AES.ID, EA.CONSTITUENTID, EA.EMAILADDRESS, EA.ID from ANONYMOUSEMAILSUBSCRIBER AES 
        inner join EMAILADDRESS EA on AES.EMAILADDRESS=EA.EMAILADDRESS

    --store records processed

    set @NUMRECORDSPROCESSED = @@ROWCOUNT

    merge dbo.MAILPREFERENCE as target
    using
    (
        select FS.CONSTITUENTID, AEP.BUSINESSUNITCODEID, AEP.APPEALCATEGORYCODEID, AEP.SITEID, ~AEP.OPTEDOUT SENDMAIL, FS.EMAILADDRESSID
        from AnonymousEmailPreference AEP
                inner join @FOUNDSUBSCRIBERS FS on AEP.ANONYMOUSEMAILSUBSCRIBERID=FS.ANONYMOUSEMAILSUBSCRIBERID
    ) as source
    on ((target.CONSTITUENTID=source.CONSTITUENTID or target.CONSTITUENTID is null and source.CONSTITUENTID is null)
        and target.MAILTYPECODE=1
        and (target.BUSINESSUNITCODEID=source.BUSINESSUNITCODEID or target.BUSINESSUNITCODEID is null and source.BUSINESSUNITCODEID is null)
        and (target.CATEGORYCODEID=source.APPEALCATEGORYCODEID or target.CATEGORYCODEID is null and source.APPEALCATEGORYCODEID is null)
        and (target.SITEID=source.SITEID or target.SITEID is null and source.SITEID is null))
    when matched then 
        update set CONSTITUENTID=source.CONSTITUENTID,
                   MAILTYPECODE=1,
                   BUSINESSUNITCODEID=source.BUSINESSUNITCODEID,
                   CATEGORYCODEID=source.APPEALCATEGORYCODEID,
                   SITEID=source.SITEID,
                   SENDMAIL=source.sendmail,
                   DELIVERYMETHODCODE=1,
                   RECEIPTTYPECODE=0,
                   EMAILADDRESSID=source.EMAILADDRESSID,
                   CHANGEDBYID=@CHANGEAGENTID,
                   DATECHANGED=@CURRENTDATE

    when not matched then
        insert (CONSTITUENTID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,SITEID,SENDMAIL,DELIVERYMETHODCODE,RECEIPTTYPECODE,EMAILADDRESSID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            values (source.CONSTITUENTID, 1, source.BUSINESSUNITCODEID, source.APPEALCATEGORYCODEID, source.SITEID, source.SENDMAIL, 1, 0, source.EMAILADDRESSID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    --cleanup migration

    delete dbo.ANONYMOUSEMAILSUBSCRIBER
        from dbo.ANONYMOUSEMAILSUBSCRIBER AES
        inner join @FOUNDSUBSCRIBERS F on AES.ID = F.ANONYMOUSEMAILSUBSCRIBERID

end