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