USP_CLONESITE_SURVEY

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


    insert into dbo.SiteSurveys(ID,ClientSitesID,Title,TitleVisible,SubTitle,SubTitleVisible,LastActivityDate,PrevButtonVisible,ExitButtonVisible,AllowMultipleSubmit,OnSubmitMsg,AllowViewResults,State,ClosedMsg,SubmitButtonText,NextButtonText,PrevButtonText,ExitButtonText)
    select
    SC.ID,
    SC.ClientSitesID,
    SV.Title,
    SV.TitleVisible,
    SV.SubTitle,
    SV.SubTitleVisible,
    SV.LastActivityDate,
    SV.PrevButtonVisible,
    SV.ExitButtonVisible,
    SV.AllowMultipleSubmit,
    SV.OnSubmitMsg,
    SV.AllowViewResults,
    SV.State,
    SV.ClosedMsg,
    SV.SubmitButtonText,
    SV.NextButtonText,
    SV.PrevButtonText,
    SV.ExitButtonText
    from dbo.SiteSurveys SV
    inner join dbo.SiteContent SC on SC.CopySourceID = SV.ID
    where SC.CopyBatchGUID = @CopyBatchGuid

    insert into dbo.SiteSurveyQuestions(SiteSurveysId,SiteSurveyQuestionTypesId,Sequence,Page,LinkText,Question,SubTitle,RandomizeAnswers,Mandatory,MaxAnswerLen,LastActivityDate,ConstantSumValue,SkipAutoNbr)
    select
    SC.ID,
    ssq.SiteSurveyQuestionTypesId,
    SSQ.Sequence,
    SSQ.Page,
    SSQ.LinkText,
    SSQ.Question,
    SSQ.SubTitle,
    SSQ.RandomizeAnswers,
    SSQ.Mandatory,
    SSQ.MaxAnswerLen,
    SSQ.LastActivityDate,
    SSQ.ConstantSumValue,
    SSQ.SkipAutoNbr
    from dbo.SiteSurveyQuestions SSQ
    inner join dbo.SiteContent SC on SC.CopySourceID = SSQ.SiteSurveysId
    where SC.CopyBatchGUID = @CopyBatchGuid

    insert into dbo.SiteSurveyAnswers(SiteSurveyQuestionsId,Sequence,Answer,NextQuestionId)
    select
    NEWSSQ.ID,
    SSA.Sequence,
    SSA.Answer,
    SSA.NextQuestionId
    from dbo.SiteSurveyAnswers SSA
    inner join dbo.SiteSurveyQuestions OLDSSQ on OLDSSQ.ID = SSA.SiteSurveyQuestionsId
    inner join dbo.SiteContent SC on SC.CopySourceID = OLDSSQ.SiteSurveysId
    inner join dbo.SiteSurveyQuestions NEWSSQ on NEWSSQ.SiteSurveysId = SC.ID and NEWSSQ.Sequence = OLDSSQ.Sequence and NEWSSQ.Page = OLDSSQ.Page
    where SC.CopyBatchGUID = @CopyBatchGuid

    insert into dbo.SiteSurveyAnswerColumns(SiteSurveyQuestionsId,Sequence,ColumnName)
    select
    NEWSSQ.ID,
    SSAC.Sequence,
    SSAC.ColumnName
    from dbo.SiteSurveyAnswerColumns SSAC
    inner join dbo.SiteSurveyQuestions OLDSSQ on OLDSSQ.ID = SSAC.SiteSurveyQuestionsId
    inner join dbo.SiteContent SC on SC.CopySourceID = OLDSSQ.SiteSurveysId
    inner join dbo.SiteSurveyQuestions NEWSSQ on NEWSSQ.SiteSurveysId = SC.ID and NEWSSQ.Sequence = OLDSSQ.Sequence and NEWSSQ.Page = OLDSSQ.Page
    where SC.CopyBatchGUID = @CopyBatchGuid

    insert into dbo.SiteSurveyAnswerColumnData(SiteSurveyAnswerColumnsId,Sequence,Data)
    select
    NEWSSAC.ID,
    SSACD.Sequence,
    SSACD.Data
    from dbo.SiteSurveyAnswerColumnData SSACD
    inner join dbo.SiteSurveyAnswerColumns OLDSSAC on OLDSSAC.ID = SSACD.SiteSurveyAnswerColumnsId
    inner join dbo.SiteSurveyQuestions OLDSSQ on OLDSSQ.ID = OLDSSAC.SiteSurveyQuestionsId
    inner join dbo.SiteContent SC on SC.CopySourceID = OLDSSQ.SiteSurveysId
    inner join dbo.SiteSurveyQuestions NEWSSQ on NEWSSQ.SiteSurveysId = SC.ID and NEWSSQ.Sequence = OLDSSQ.Sequence and NEWSSQ.Page = OLDSSQ.Page
    inner join dbo.SiteSurveyAnswerColumns NEWSSAC on NEWSSAC.SiteSurveyQuestionsId = NEWSSQ.ID and NEWSSAC.Sequence = OLDSSAC.Sequence
    where SC.CopyBatchGUID = @CopyBatchGuid