spCloneSite_Images

CREATE procedure [dbo].[spCloneSite_Images]
(
    @SourceClientSitesID int,
    @TargetClientSitesID int,
    @CurrentUserID int,
    @CopyBatchGUID uniqueidentifier
)
as
begin

    set nocount on;

    begin transaction

        INSERT INTO SiteImages
            (ClientSitesID, Name, Category, FileName, UploadFileName, ImageType, ImageSize, ImageContent, Guid, OwnerID, Deleted, Type, FolderID, Caption, Approved, Private, Photographer, PhotoDateTaken, CreateDate, CopySourceID, CopyBatchGUID, URL)
        SELECT
            @TargetClientSitesID,
            Name,
            Category,
            FileName,
            UploadFileName,
            ImageType,
            ImageSize,
            ImageContent,
            newid(),
            SiteImages.OwnerID,
            SiteImages.Deleted,
            SiteImages.Type,
            isnull(SiteFolders.FolderID,0),
            Caption,
            Approved,
            Private,
            Photographer,
            PhotoDateTaken,
            getutcdate(),
            SiteImages.ID,
            @CopyBatchGUID,
            URL
        FROM dbo.SiteImages
        LEFT JOIN dbo.SiteFolders ON SiteFolders.CopySourceID = SiteImages.FolderID AND SiteFolders.CopyBatchGuid = @CopyBatchGuid
        LEFT OUTER JOIN
            (select PAGEMODELITEM.OBJECTGUID
             from dbo.PAGEMODELITEM 
             inner join dbo.PAGEMODEL on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
             where PAGEMODEL.LOCKARTIFACTS = 1
             ) PMI on PMI.OBJECTGUID = SiteImages.Guid
        WHERE
            SiteImages.ClientSitesID = @SourceClientSitesID AND
            SiteImages.Deleted = 0 AND
            SiteImages.Type = 0 AND
            PMI.OBJECTGUID is null

        -- copy over the tags for the images we have just copied

        insert into dbo.SITEIMAGETAG (
            SITEIMAGEID,
            TAGCODEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        ) select
            imageNew.ID,
            tag.TAGCODEID,
            tag.ADDEDBYID,
            tag.CHANGEDBYID,
            tag.DATEADDED,
            tag.DATECHANGED
        from dbo.SITEIMAGETAG tag
        inner join dbo.SITEIMAGES imageOld on tag.SITEIMAGEID = imageOld.ID
        inner join dbo.SITEIMAGES imageNew on imageNew.CopySourceID = imageOld.ID and imageNew.CopyBatchGuid = @CopyBatchGuid

    commit transaction
end