USP_CLONESITE_TARGETEDCONTENT

CREATE procedure dbo.USP_CLONESITE_TARGETEDCONTENT
(
@SourceClientSitesID int,
@TargetClientSitesID int,
@CurrentUserID int,
@CopyBatchGuid uniqueidentifier
)
as



--Insert new entries into TargetedContentPart

insert into dbo.TargetedContentPart (Name, SiteContentID, Guid)
select TCP.Name, SC.ID, NEWID()
from dbo.TargetedContentPart TCP
inner join dbo.SiteContent SC on SC.CopySourceID = TCP.SiteContentID
where SC.CopyBatchGUID = @CopyBatchGuid

--Find matches between the original and copied eCardPart entries

declare @TCPartMapping table ([SourcePartID] int, [NewPartID] int)
insert into @TCPartMapping
select S.ID, T.ID
from dbo.TargetedContentPart S
inner join dbo.SiteContent SC on SC.CopySourceID = S.SiteContentID
inner join dbo.TargetedContentPart T on T.SiteContentID = SC.ID
where SC.CopyBatchGUID = @CopyBatchGuid

--Insert new entries into TargetedContentChecks

insert into dbo.TargetedContentChecks (TargetedPartID, PartOrder, ContentID)
select TCPM.NewPartID, TCC.PartOrder, ISNULL(SC.ID,TCC.ContentID)
from dbo.TargetedContentChecks TCC
inner join @TCPartMapping TCPM on TCPM.SourcePartID = TCC.TargetedPartID
left outer join dbo.SiteContent SC on SC.CopySourceID = TCC.ContentID and SC.CopyBatchGUID = @CopyBatchGuid

--Update XML Data

    declare @Data xml
    set @Data = 
    (
        select P.SiteContentID, TCPM.NewPartID as Value
        from dbo.UFN_GETXMLPROPERTYFORSITECOPY(@CopyBatchGuid,66, 'TargetedContentPartID') P
        inner join @TCPartMapping TCPM on TCPM.SourcePartID = P.Value
        FOR XML RAW, ELEMENTS
    )    
    exec USP_UPDATEXMLPROPERTYFORSITECOPY 'TargetedContentPartID', @Data