Cascade copy records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Cascade copy records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-17 : 11:31:59

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)."

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-06-17 : 11:55:32

This is not tested, but should be close to what you are looking for. I know I'm a loser, but the only thing I could think of right off used a cursor. Someone please post a nice set based solution.

 
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

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 06/18/2002 10:18:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources