Como adiciono vários novos registros com base nos antigos

Resposta à pergunta original

Deixando de lado por enquanto que a) não sabemos o que get a copy of ...implica eb) não temos o DDL para as tabelas em questão (por exemplo, definições de PK) ... teremos que improvisar um pouco ...

Começaremos com algumas tabelas e dados de amostra:

drop table if exists Surveys
drop table if exists Questions
drop table if exists Offerings;

create table Surveys
(SurveyID    int           identity
,sname       varchar(30)
);

create table Questions
(SurveyID    int
,QuestionID  int           identity
,qtext       varchar(max)
);

create table Offerings
(SurveyID    int
,QuestionID  int
,OfferingID  int            identity
,otext       varchar(max)
);

-- populate the tables

declare @new_sid int

insert into Surveys (sname) select 'Survey ABC'

select @new_sid = @@identity

insert into Questions (SurveyID, qtext) values 
(@new_sid, 'Quest_#1'), 
(@new_sid, 'Quest_#2'), 
(@new_sid, 'Quest_#3')

-- just pick 2 questions to work with; min()/max() will suffice

insert into Offerings (SurveyID, QuestionID, otext) 
select @new_sid,min(QuestionID),'Off_#1' from Questions where SurveyID = @new_sid
union all
select @new_sid,max(QuestionID),'Off_#2' from Questions where SurveyID = @new_sid;

-- review our new data

select * from Surveys   order by SurveyID
select * from Questions order by SurveyID, QuestionID
select * from Offerings order by SurveyID, QuestionID, OfferingID;

 SurveyID | sname     
 -------- | ----------
        1 | Survey ABC

 SurveyID | QuestionID | qtext    
 -------- | ---------- | ---------
        1 |          1 | Quest_#1 
        1 |          2 | Quest_#2 
        1 |          3 | Quest_#3

 SurveyID | QuestionID | OfferingID | otext 
 -------- | ---------- | ---------- | ------
        1 |          1 |          1 | Off_#1
        1 |          3 |          2 | Off_#2

Para este exemplo, afirmaremos que copysignifica duplicar o mesmo número de linhas (Pesquisa, Perguntas, Ofertas) e anexar todos os nossos valores de texto com ' (versão_#2)':

declare @old_sid int,
        @new_sid int

-- find our old SurveyID

select @old_sid = SurveyID from Surveys where sname = 'Survey ABC'

-- create our new Survey, appending '(version_#2)' to stext

insert into Surveys (sname) 
select sname + ' (version_#2)' from Surveys where SurveyID = @old_sid

-- grab the new SurveyID

select @new_sid = @@identity

-- copy our old Survey's Questions, replacing the old SurveyID with the new SurveyID,
-- and append ' (version_#2)' to the qtext field

insert into Questions (SurveyID, qtext) 
select @new_sid, 
       q.qtext + ' (version_#2)' 
from   Questions q
where  q.SurveyID = @old_sid;

-- use a couple CTEs to pull our old Questions/Offerings and new Questions;
-- without any input on how to map the old and new data sets we'll generate
-- a row_number() column with each data set, with the objective being to 
-- join the old/new data sets by matching row numbers

with
oldtab as
(select  row_number() over(order by q.QuestionID) as rnum, 
         o.otext
 from    Questions q 
 left 
 join    Offerings o
 on      q.SurveyID   = o.SurveyID
 and     q.QuestionID = o.QuestionID
 where   q.SurveyID   = @old_sid),

newtab as
(select  row_number() over(order by q.QuestionID) as rnum, 
         q.SurveyID, 
         q.QuestionID
 from    Questions q 
 where   q.SurveyID   = @new_sid)

-- insert our new SurveyID/QuestionID pairs, and the old otext values

insert into Offerings (SurveyID, QuestionID, otext) 

select   n.SurveyID,
         n.QuestionID,
         o.otext + ' (version_#2)'
from     oldtab o
join     newtab n
on       o.rnum = n.rnum       -- join by row_number()
where    o.otext is NOT NULL   -- skip (old) Questions that didn't have a matching row in Offerings
order by o.rnum

E os resultados:

select * from Surveys   order by SurveyID
select * from Questions order by SurveyID, QuestionID
select * from Offerings order by SurveyID, QuestionID, OfferingID

 SurveyID | sname                 
 -------- | ----------------------
        1 | Survey ABC            
        2 | Survey ABC (version_#2)

 SurveyID | QuestionID | qtext               
 -------- | ---------- | --------------------
        1 |          1 | Quest_#1            
        1 |          2 | Quest_#2            
        1 |          3 | Quest_#3            
        2 |          4 | Quest_#1 (version_#2)
        2 |          5 | Quest_#2 (version_#2)
        2 |          6 | Quest_#3 (version_#2)

 SurveyID | QuestionID | OfferingID | otext              
 -------- | ---------- | ---------- | -------------------
        1 |          1 |          1 | Off_#1             
        1 |          3 |          2 | Off_#2             
        2 |          4 |          3 | Off_#1 (version_#2)
        2 |          6 |          4 | Off_#2 (version_#2)

Aqui está um dbfiddle do acima.

Resposta à pergunta atualizada/editada (adição de colunas qtext/qtype/ovalues)

Usando o mesmo create tablee os insertcomandos da parte editada da pergunta, reutilizaremos parte do código da minha resposta original ...

declare @old_sid int,
        @new_sid int

select @old_sid = SurveyID from Survey where sname = 'New Survey'

-- create new Survey record

insert into Survey (sname) 
select sname + ' 2' from Survey where SurveyID = @old_sid

select @new_sid = @@identity

-- create new Questions records

insert into Questions (SurveyID, qtext, qtype) 
select @new_sid, 
       q.qtext,
       q.qtype
from   Questions q
where  q.SurveyID = @old_sid;

-- create new Offerings records

with
qmap as
(-- build a mapping between old and new QuestionID/SurveyID pairs
 select o.QuestionID as oldQID,
        o.SurveyID   as oldSID,
        n.QuestionID as newQID,
        n.SurveyID   as newSID,
        n.qtext,
        n.qtype

from    Questions o
join    Questions n

on      o.qtext = n.qtext
and     o.qtype = n.qtype
and     o.SurveyID = @old_sid
and     n.SurveyID = @new_sid)

insert into Offerings (QuestionID, SurveyID, ovalues)

select  m.newQID,
        m.newSID,
        o.ovalues

from    Offerings o
join    qmap m

on      o.QuestionID = m.oldQID
and     o.SurveyID   = m.oldSID
and     o.SurveyID   = @old_sid;

NOTA: Provavelmente poderia rolar esta qmapsolução na minha resposta à pergunta original, mas optando por deixar a resposta original como está por enquanto.

E os resultados:

select * from Survey    order by SurveyID
select * from Questions order by SurveyID, QuestionID
select * from Offerings order by SurveyID, QuestionID, OfferingID

 SurveyID | sname       
 -------- | ------------
        1 | New Survey  
        2 | New Survey 2

 QuestionID | SurveyID | qtext      | qtype         
 ---------- | -------- | ---------- | --------------
          1 |        1 | Enter text | SingleAnswer  
          2 |        1 | Pick one   | MultipleChoice
          3 |        2 | Enter text | SingleAnswer  
          4 |        2 | Pick one   | MultipleChoice

 OfferingID | QuestionID | SurveyID | ovalues 
 ---------- | ---------- | -------- | --------
          1 |          2 |        1 | Choice 1
          2 |          2 |        1 | Choice 2
          3 |          2 |        1 | Choice 3
          4 |          4 |        2 | Choice 1
          5 |          4 |        2 | Choice 2
          6 |          4 |        2 | Choice 3

Aqui está um dbfiddle para o acima.