0

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
2
  • I assume that you're using SQL Server? If so you can use OUTPUT stackoverflow.com/questions/95988/…
    – indiri
    Sep 27, 2017 at 20:19
  • what are the PKs for the tables in question? one problem I see is in how to go from Questions to Offerings while maintaining a one-to-one mapping between the old and new SurveyID/QuesionID identity pairs (eg, 1/1 maps to 2/4, 1/2 maps to 2/5, 1/3 maps to 2/6) ("Duh, Mark!" ?)
    – markp-fuso
    Sep 27, 2017 at 23:48

2 Answers 2

0

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.

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.

5
  • See, I knew you guys were smart.
    – Darrell
    Sep 28, 2017 at 19:51
  • yeah, and we're cheap, too; see What should I do when someone answers my question?; say, buddy, can you spare a couple clicks ... upvote answer? accept answer? :-P
    – markp-fuso
    Sep 28, 2017 at 19:57
  • Sorry, took to long to formulate my comment. To continue...Now, let's add a wrinkle. Add a field to Questions called questiontype and make it multiple choice. Then, in the Offerings table, you'll need, say, 3 different answers for for that one question. (Hope that makes sense)
    – Darrell
    Sep 28, 2017 at 19:59
  • At a minimum you need to update the question as few folks are going to scrounge through comments looking for add-ons; but then an updated question has the potential to confuse other folks when they try to match the latest/greatest (updated) question with an answer to a previous/older version of the question; I'd recommend you ask a new question after reviewing these links: how to ask a good question and how to create a minimal, complete and verifiable example; hint: current question is lacking details
    – markp-fuso
    Sep 28, 2017 at 20:09
  • Added clarification to original question.
    – Darrell
    Sep 28, 2017 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.