Cascade copy of rows in sql

Question

I found this thread here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16836 I have exactly the same problem. Quote:

Rob Pearmain writes "I have 3 tables that hold questions.

Table 1 : Question

Field : ID (Unique) Field : Name (Text)

Table 2 : Question Text (References Table1-ID)

Field : ID (Unique) Field : QuestionID (integer ref to Table1 ID) Field : Text

Table 3 : Options

Field : ID (Unique) Field : QuestionTextID (integer ref to Table2 ID) Field : Text

Say for example, I create a question with 2 Question text records and 5 option records. If I wanted to duplicate that question to a new question, and copy over the Question Text records to new ID's, and all the related options, how can I do this easily (As the duplicate question will have a new ID, each of the duplicated question text's will have new ID's as will each of the options)."

The suggested solution is:

create procedure CopyQuestion
@idtocopy int
AS
declare @tempquestionid
declare @tempquestiontextid
declare @questiontextid

insert into question (name)
     select name from question where id = @idtocopy

select @tempquestionid = @@identity

declare question_cursor cursor for
     select id from [question text] where id = @idtocopy
open question_cursor
fetch next from question_cursor into @questiontextid
while @@fetch_status = 0
begin
   insert into [question text] (questionid, text)
      select @tempquestionid, text from [question text] where id = @questiontextid
   select @tempquestiontextid = @@identity
      insert into [options] (questiontextid, text)
   select @tempquestiontextid, text from [options] where questiontextid = @questiontextid
   fetch next from question_cursor into @questiontextid
end
close question_cursor
deallocate question_cursor

Is there a better solution to this problem? I will use an insert trigger. Thanks!

Answer 1

You can use the merge statement with the output clause to get a match between the old and new id in questionText. This is described in this question Using merge..output to get mapping between source.id and target.id.

In your case the code would look something like this. The code is not tested so there might be any number of typos in there but it shows what you can do.

create procedure CopyQuestion
  @idtocopy int
as

declare @QuestionID int

insert into question
select Name 
from question 
where ID = @idtocopy

select @QuestionID = scope_identity() 

declare @IDs table (NewQID int, OldQID int)

merge questionText as T
using (select ID, @QuestionID as QuestionID, Field
       from questionText
       where QuestionID = @idtocopy) as S
on 0=1
when not matched then
  insert (QuestionID, Field) values (QuestionID, Field)
output inserted.ID, S.ID   into @IDs;       

insert into options
select 
    I.NewQID,
    O.Field
from options O
  inner join @IDs as I
    on O.QuestionTextID = I.OldQID

Answer 2

This is another way to do the same thing a little bit more set based. In my below example I used a temp table to map the IDs between the two new tables. Also please remove spaces from your table names (just because you can doesn't mean you should).

CREATE PROCEDURE udf_COPY_QUESTION 
@ID_TO_COPY int
 as 
BEGIN TRANSACTION
BEGIN TRY
DECLARE @NEW_QUESTION_ID INT, @MAX_ID INT
insert into question (name)
     select name from question where id = @ID_TO_COPY
SET @NEW_QUESTION_ID = SCOPE_IDENTITY()
SET @MAX_ID =IDENT_CURRENT( 'question text' )
select @NEW_QUESTION_ID AS questionid,
       Text,
       ROW_NUMBER() OVER (ORDER NAME) + @MAX_ID as new_text_id, 
       id as old_text_id
 INTO  #TEMP  from [question text] 
      where questionid = @ID_TO_COPY         
insert into [question text] (QuestionID,Text)
      select questionid,Text from #TEMP 
      order by new_text_id
insert into Options  (questiontextid, text) 
      select t.new_text_id,o.Text from options o
      inner join #temp t on t.old_text_id = o.questiontextid
COMMIT TRANSACTION
END TRY
BEGIN CATCH
    RAISERROR('COPY FAILED',10,1)
    ROLLBACK TRANSACTION 
END CATCH