How do I insert into Table2 from Table1 and then use the inserted ID to insert into Table3

Question

I have some tables that store the following data, which is not exactly what I actually have but is simplified so that it's easier to explain..

Table1

UserID (FK)
TestID (FK)
StartTime
EndTime
QuestionID1
AnswerToQuestion1
QuestionID2
AnswerToQuestion2

Since Table1 is a spreadsheet, I want to import this data into two tables (Table2 and Table3)

Table2

UserTestID (PK)
UserID
TestID
StartTime
EndTime

Table3

UserTestID (FK)
QuestionID (FK)
AnswerID (FK)

What I want to do is ...

I want to do the following insertion for each row in Table1.

INSERT INTO Table2
(
    UserID
    ,TestID
    ,StartTime
    ,EndTime
)
SELECT UserID
       ,TestID
       ,StartTime
       ,EndTime
FROM Table1


Declare @InsertedUserTestID INT
Set @id = SCOPE_IDENTITY()


-- Do this twice (once for Question1 and the second for Question2)
INSERT INTO Table3
(
    UserTestID
    ,QuestionID
    ,AnswerID
)
SELECT 
    @id
    ,QuestionID1
    ,AnswerToQuestion1
FROM Table1

What I've tried

I read that I can use OUTPUT to use the inserted ID but it doesn't work because it doesn't let me select those columns that are not used to insert into Table2.

INSERT INTO Table2
(
    UserID
    ,TestID
    ,StartTime
    ,EndTime
)
OUTPUT Inserted.UserTestID 
INTO Table3(Inserted.UserTestID, QuestionID1, AnswerToQuestion1)
SELECT UserID
       ,TestID
       ,StartTime
       ,EndTime
FROM Table1

Is there any way to achieve this? Or should I do the insertion from Table1 to Table2 first, and then use both Table1 and Table2 to insert into Table3?

http://stackoverflow.com/q/5365629/73226 – Martin Smith May 27 '15 at 00:40 — Martin Smith, May 27 '15 at 00:40

score 2 · Accepted Answer · answered May 27 '15 at 00:51

Martin is right - use MERGE for this. You could do it all in one statement if you didn't have two row entries to INSERT - as such you could dump to a temp table and do the following:

CREATE TABLE #answers (UserTestID INT, QuestionID1 INT, AnswerID1 INT, QuestionID2 INT, AnswerID2 INT);

MERGE Table2
USING 
(  
    SELECT UserID
           ,TestID
           ,StartTime
           ,EndTime
           ,QuestionID1
           ,AnswerToQuestion1
           ,QuestionID2
           ,AnswerToQuestion2
    FROM Table1
) src ON (1=0)
WHEN NOT MATCHED THEN INSERT (UserID, TestID, StartTime, EndTime)
 VALUES (src.UserID, src.TestID, src.StartTime, src.EndTime
OUTPUT Inserted.UserTestID, src.QuestionID1, src.AnswerID1, src.QuestionID2, src.AnswerID2
INTO #answers(UserTestID, QuestionID1, AnswerID1, QuestionID2, AnswerID2);

INSERT INTO Table3
(
    UserTestID
    ,QuestionID
    ,AnswerID
)
SELECT UserTestID
    ,QuestionID1
    ,AnswerID1
FROM #answers
UNION
SELECT UserTestID
    ,QuestionID2
    ,AnswerID2
FROM #answers;