Ok this is the solution I worked out. I've written the whole 'proof' here so you can run it and see how it works. The result is what I was after - a resultset which maps the original row IDs I want to copy to the newly created row IDs.
Forget the "clients" example above, it seems to be confusing the issue. This example shows how a survey, along with its related questions, is copied to a new survey, and the resultset contains the new survey ID along with a map between the original questions IDs and the new question IDs. That mapping of IDs is what I was after.
DECLARE @Surveys TABLE
(
SurveyId INT IDENTITY PRIMARY KEY,
SurveyTitle VARCHAR(100) NOT NULL,
IsTemplate TINYINT NOT NULL DEFAULT 0
)
DECLARE @Questions TABLE
(
QuestionId INT IDENTITY PRIMARY KEY,
SurveyId INT NOT NULL,
QuestionNumber SMALLINT NOT NULL,
QuestionText VARCHAR(1000) NOT NULL
)
-- Couple of template surveys. IDs will be 1 and 2
INSERT INTO @Surveys (SurveyTitle, IsTemplate)
VALUES
('Survey 1', 1),
('Survey 2', 1);
-- Questions for survey 1. IDs will be 1 and 2
INSERT INTO @Questions (SurveyId, QuestionNumber, QuestionText)
VALUES
(1, 1, 'Question 1'),
(1, 2, 'Question 2');
-- Questions for survey 2. IDs will be 3 and 4
INSERT INTO @Questions (SurveyId, QuestionNumber, QuestionText)
VALUES
(2, 1, 'Another Question 1'),
(2, 2, 'Another Question 2');
-- Clone survey 1 and get the new survey ID
DECLARE @CopySurveyId INT = 1;
DECLARE @TempTable TABLE (Id INT);
DECLARE @NewSurveyId INT;
INSERT INTO @Surveys (SurveyTitle, IsTemplate)
OUTPUT INSERTED.SurveyId INTO @TempTable
SELECT SurveyTitle, 0 FROM @Surveys WHERE SurveyId = @CopySurveyId;
SELECT @NewSurveyId = Id FROM @TempTable; -- Put new SurveyId into scalar variable.
-- This is what I was looking for:
-- Clone questions from template into the new survey
-- and get a "lookup table" mapping the original question IDs to the new question IDs.
DECLARE @QuestionMap TABLE (NewSurveyId INT, TemplateQuestionId INT, NewQuestionId INT);
MERGE @Questions USING (
SELECT QuestionId, SurveyId, QuestionNumber, QuestionText
FROM @Questions
WHERE SurveyId = @CopySurveyId
) q ON 0 = 1 -- ensure always not matched
WHEN NOT MATCHED
THEN INSERT (SurveyId, QuestionNumber, QuestionText)
VALUES (@NewSurveyId, q.QuestionNumber, q.QuestionText)
OUTPUT @NewSurveyId, q.QuestionId, INSERTED.QuestionId INTO @QuestionMap; -- original ID and new ID
SELECT * FROM @QuestionMap -- yay!
The resultset from the above is this:
NewSurveyID TemplateQuestionId NewQuestionId
3 1 5
3 2 6
With thanks to this post on SO for suggesting the MERGE solution: https://stackoverflow.com/a/8774519/471597