How do I add multiple new records based on old ones

Question

Ok all you smart folks, help me figure this out.

I have a set of related tables for surveys we are doing:

dbo.Surveys
------------
| SurveyID |
------------
| 1        |
------------

dbo.Questions
-------------------------
| SurveyID | QuestionID |
-------------------------
| 1        | 1          |
-------------------------
| 1        | 2          |
-------------------------
| 1        | 3          |
-------------------------

dbo.Offerings
--------------------------------------
| SurveyID | QuestionID | OfferingID |
--------------------------------------
| 1        | 1          | 1          |
--------------------------------------
| 1        | 3          | 2          |
--------------------------------------

Facts:

dbo.Surveys.SurveyID is IDENTITY

dbo.Questions.QuestionID is IDENTITY

dbo.Offerings.OfferingID is IDENTITY

I need to create a new survey based on a copy of one that exists.

I get a copy of SurveyID 1 and insert it into dbo.Surveys then get the new SurveyID.

I get a copy of all the Questions related to SurveyID 1 and insert them into dbo.Questions and change the SurveyID to the new one.

This of course generates a new QuestionID for each record.

I get a copy of all the Offerings related to SurveyID 1 and insert them into dbo.Offerings and change the SurveyID to the new one.

The trick now is, how do I know which new OfferingID goes with which new QuestionID so I can update the QuestionID in the dbo.Offerings table?

So, to flesh this out, this is what I end up with:

dbo.Surveys
------------
| SurveyID |
------------
| 1        |
------------
| 2        |
------------

dbo.Questions
-------------------------
| SurveyID | QuestionID |
-------------------------
| 1        | 1          |
-------------------------
| 1        | 2          |
-------------------------
| 1        | 3          |
-------------------------
| 2        | 4          |
-------------------------
| 2        | 5          |
-------------------------
| 2        | 6          |
-------------------------

dbo.Offerings
--------------------------------------
| SurveyID | QuestionID | OfferingID |
--------------------------------------
| 1        | 1          | 1          |
--------------------------------------
| 1        | 3          | 2          |
--------------------------------------
| 2        | 1          | 3          | (QuestionID needs to be 4)
--------------------------------------
| 2        | 3          | 4          | (QuestionID needs to be 6)
--------------------------------------

EDIT: Ok. For the sake of clarification:

CREATE TABLE Survey (SurveyID int identity, sname varchar(30));
CREATE TABLE Questions (QuestionID int IDENTITY, SurveyID int, qtext varchar(30), qtype varchar(20));
CREATE TABLE Offerings (OfferingID int IDENTITY, QuestionID int, SurveyID int, ovalues varchar(30));

INSERT INTO Survey VALUES ('New Survey');
INSERT INTO Questions VALUES (1,'Enter text','SingleAnswer');
INSERT INTO Questions VALUES (1,'Pick one','MultipleChoice');
INSERT INTO Offerings VALUES (2,1,'Choice 1');
INSERT INTO Offerings VALUES (2,1,'Choice 2');
INSERT INTO Offerings VALUES (2,1,'Choice 3');

SELECT * FROM Survey;
SELECT * FROM Questions;
SELECT * FROM Offerings;

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

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

    OfferingID | QuestionID | SurveyID | ovalues
    ---------------------------------------------
             1 |          2 |        1 | Choice 1
             2 |          2 |        1 | Choice 2
             3 |          2 |        1 | Choice 3

So, now I need a copy of SurveyID 1 such that:

    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

Answer 1

Response to Updated/Edited question (addition of qtext/qtype/ovalues columns)

---

Using the same create table and insert commands from the edited portion of the question, we'll re-use some of the code from my original answer ...

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;

NOTE: Could probably roll this qmap solution into my answer to the original question, but opting to leave the original answer as is for now.

And the results:

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

Here's a dbfiddle for the above.

Answer 2

Response to Original question

---

Setting aside for the moment that we a) don't know what get a copy of ... entails and b) don't have the DDL for the tables in question (eg, PK definitions) ... we'll have to wing this a bit ...

We'll start with some sample tables and data:

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

For the sake of this example we'll assert that copy means to duplicate the same number of rows (Survey, Questions, Offerings) and append all of our text values with ' (version_#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

And the results:

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)

Here's a dbfiddle of the above.