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 
Cascade copy records 
Posted - 2002-06-17 : 11:31:59
