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!

I haven't looked closely at the rest of the logic yet, but please stop using @@IDENTITY - use SCOPE_IDENTITY() instead. So question has id and name, and you can create a new question with the exact same name but a new surrogate identity value? How will a user tell them apart - do they have to memorize the ID? — Aaron Bertrand, Jul 18 '11 at 01:36
I will. I'm more concerned about the cursor, and I was thinking there's a better way of doing it — user194076, Jul 18 '11 at 01:39
Don't understand the logic. Should the cursor actually say WHERE questionid = @idtocopy? You seem to be mixing up the use of a column called "id"... does this cursor actually do what you expect it to? Can you show some sample data and desired results? Also, is your data type really TEXT? You should be using VARCHAR or NVARCHAR in SQL Server 2008. TEXT is deprecated. — Aaron Bertrand, Jul 18 '11 at 01:46

score 4 · Accepted Answer · edited May 23 '17 at 12:11

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

+1 for MERGE. That seems like the best answer for SQL Server 2008. — rsbarro, Jul 18 '11 at 14:39

JStead · Answer 2 · 2011-07-18T04:38:54.097

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

Neat approach, but it might fail if you don't wrap the whole thing in a transaction. If other users are attempting to copy or add questions when this is running the ID's might get out of sync. — rsbarro, Jul 18 '11 at 02:48
Also might be a little dangerous to assume that QuestionText.ID values are given out sequentially (in theory anyway). That's one of the reasons why I think a cursor might be the best way to solve this problem. — rsbarro, Jul 18 '11 at 02:55
I also thought of this as well but if the only field is the text of question whether the options get paired up with text1 or text2 containing the same text the end result of the copy is exactly the same. I agree on the transaction front. — JStead, Jul 18 '11 at 04:35

Cascade copy of rows in sql

2 Answers2

Linked