spBulkUpdateEmailProjectAppeal_Duplicates

CREATE PROCEDURE [dbo].[spBulkUpdateEmailProjectAppeal_Duplicates]
(@AppealID int)

AS

        --Clear 'em out... yaw
        DELETE [EmailProjectAppeal_Duplicates] WHERE EmailProjectAppealID = @AppealID

        DECLARE @DuplicateEmails TABLE (EmailAddress nvarchar(255))

        --Records found in both an imported list and RE list
        INSERT INTO @DuplicateEmails 
        SELECT DISTINCT elp.EmailAddress FROM EmailList_People elp
                INNER JOIN EmailListUploadMasterList ml on elp.EmailAddress = ml.EmailAddress
                INNER JOIN EmailProjectAppeal_EmailList epael ON elp.EmailListID = epael.EmailListID 
                WHERE epael.EmailProjectAppealID = @AppealID AND epael.AppealListTYpe = 1 AND ISNULL(elp.EmailAddress, '') <> ''
                AND EXISTS(SELECT NULL FROM EmailListUploadMasterList ml INNER JOIN EmailProjectAppeal_EmailList epael ON
                ml.EmailListID = epael.EmailListID WHERE epael.EmailProjectAppealID = @AppealID AND epael.AppealListTYpe = 1)

        --Records found in an Imported List that are duped with another imported list
        INSERT INTO @DuplicateEmails 
        SELECT DISTINCT ml.EmailAddress FROM EmailListUploadMasterList ml
        INNER JOIN EmailProjectAppeal_EmailList epael on ml.EmailListID = epael.EmailListID
        WHERE epael.EmailProjectAppealID = @AppealID AND ISNULL(ml.EmailAddress, '') <> '' AND epael.AppealListTYpe = 1
        AND NOT EXISTS(SELECT NULL FROM @DuplicateEmails de WHERE de.EmailAddress = ml.EmailAddress)
        GROUP BY ml.EmailAddress
        HAVING Count(ml.EmailAddress) > 1

        --Records found in a RE List that are duped with another RE list
        INSERT INTO @DuplicateEmails 
        SELECT DISTINCT elp.EmailAddress FROM EmailList_People elp
        INNER JOIN EmailProjectAppeal_EmailList epael on elp.EmailListID = epael.EmailListID
        WHERE epael.EmailProjectAppealID = @AppealID AND ISNULL(elp.EmailAddress, '') <> '' AND epael.AppealListTYpe = 1
        AND NOT EXISTS(SELECT NULL FROM @DuplicateEmails de WHERE de.EmailAddress = elp.EmailAddress)
        GROUP BY elp.EmailAddress
        HAVING Count(elp.EmailAddress) > 1 

        --BEGIN Insert into duplicates table

        INSERT INTO [EmailProjectAppeal_Duplicates]
        SELECT ml.Emailaddress, ml.EmailDisplayName, @AppealID, ml.EmailListID 
        FROM EmailListUploadMasterList ml
        INNER JOIN @DuplicateEmails de on ml.EmailAddress = de.EmailAddress
        INNER JOIN EmailProjectAppeal_EmailList epael on ml.EmailListID = epael.EmailListID
        WHERE epael.EmailProjectAppealID = @AppealID AND epael.AppealListTYpe = 1

        INSERT INTO [EmailProjectAppeal_Duplicates]
        SELECT elp.Emailaddress, elp.EmailDisplayName, @AppealID, elp.EmailListID 
        FROM EmailList_People elp
        INNER JOIN @DuplicateEmails de on elp.EmailAddress = de.EmailAddress
        INNER JOIN EmailProjectAppeal_EmailList epael on elp.EmailListID = epael.EmailListID
        WHERE  epael.EmailProjectAppealID = @AppealID AND epael.AppealListTYpe = 1