spCloneSite_Folders

CREATE PROCEDURE [dbo].[spCloneSite_Folders]
(
    @SourceClientSitesID int,
    @TargetClientSitesID int,
    @CurrentUserID int,
    @CopyBatchGUID uniqueidentifier
)

AS

BEGIN TRANSACTION

    INSERT INTO SiteFolders
    (FolderName, ParentFolderID, Sequence, ClientSitesID, Type, Deleted, Guid, OwnerID, CopySourceID, CopyBatchGUID)
    SELECT
    FolderName,
    NULL,
    Sequence,
    @TargetClientSitesID,
    Type,
    Deleted,
    newid(),
    OwnerID,
    FolderID,
    @CopyBatchGUID
    FROM SiteFolders WHERE ClientSitesID = @SourceClientSitesID AND Deleted = 0 and Type not in (1,8)   --Do not copy folders of email campaign and email list



    UPDATE NewSiteFolders
    SET NewSiteFolders.ParentFolderID = NewParentFolders.FolderID
    FROM SiteFolders NewSiteFolders
    INNER JOIN SiteFolders CopySiteFolders ON CopySiteFolders.FolderID = NewSiteFolders.CopySourceID
    INNER JOIN SiteFolders CopyParentFolders ON CopyParentFolders.FolderID = CopySiteFolders.ParentFolderID
    INNER JOIN SiteFolders NewParentFolders ON NewParentFolders.CopySourceID = CopyParentFolders.FolderID AND NewParentFolders.CopyBatchGUID = @CopyBatchGUID
    WHERE
    NewSiteFolders.CopyBatchGUID = @CopyBatchGUID

COMMIT TRANSACTION