how to make a copy of row in a table in that table itself by passing one parameter @oldquestionbank uniqueidentifier RSS

• • Reply
  

  sivaganesh12...

  Member

  227 Points

  308 Posts

  how to make a copy of row in a table in that table itself by passing one parameter @oldquestio...

  Nov 17, 2012 04:35 AM|LINK

  here i am having  two table  @question and @questionchoice  though the  questionid  wil be the foregin key for  @questionchoice from @question table 

  here is the  two table which i mentioned

  DECLARE @question table
  (
   questionid int identity(1,1),
   questionbank uniqueidentifier,
   indexnumber int,
   question varchar(20),
   crdate  datetime
   )
  
   
   insert into @question
   select 'B7152F93-45CE-4485-9672-7E0E5F62F775',1,'q1','2012-11-11 10:23:23.910' union all
   select 'B7152F93-45CE-4485-9672-7E0E5F62F775',2,'q2','2012-11-11 10:23:23.910'
   
   
   
   DECLARE @questionchoice table
  (
   choiceid int identity(1,1),
   questionid int,
   indexnumber int,
   choice varchar(20),
   isactive bit
   )
  insert into @questionchoice
   select 1,1,'A',1 union all
   select 1,2,'b',1 union all
   select 1,3,'c',1 union all
   select 2,1,'a',1 union all
   select 2,2,'b',1
   
   
   select * from @question
    select * from @questionchoice

  
  and right  now  i just trying  a store proc  in which i will pass only one parameter  @oldquestionbank uniqueidentifier and  i want to copy of the question  and choice in the same table

  i tried like this

  DECLARE @question table
  (
   questionid int identity(1,1),
   questionbank uniqueidentifier,
   indexnumber int,
   question varchar(20),
   crdate  datetime
   )
  
   
   insert into @question
   select 'B7152F93-45CE-4485-9672-7E0E5F62F775',1,'q1','2012-11-11 10:23:23.910' union all
   select 'B7152F93-45CE-4485-9672-7E0E5F62F775',2,'q2','2012-11-11 10:23:23.910'
   
   
   
   DECLARE @questionchoice table
  (
   choiceid int identity(1,1),
   questionid int,
   indexnumber int,
   choice varchar(20),
   isactive bit
   )
  insert into @questionchoice
   select 1,1,'A',1 union all
   select 1,2,'b',1 union all
   select 1,3,'c',1 union all
   select 2,1,'a',1 union all
   select 2,2,'b',1
   
   
   select * from @question
    select * from @questionchoice
    
    
   
    declare @oldquestionbank uniqueidentifier ='B7152F93-45CE-4485-9672-7E0E5F62F775'
    
   declare @questionbank uniqueidentifier
   set @questionbank=NEWID()
   insert into @question
   select @questionbank as questionbank,q.indexnumber,q.question,GETDATE()
    from @question q  where q.questionbank=@oldquestionbank

  plz tell me  how can i get  copy of  choice in choice table

   declare @questionbank uniqueidentifier
   set @questionbank=NEWID()
   insert into @question
   select @questionbank as questionbank,q.indexnumber,q.question,GETDATE()
    from @question q  where q.questionbank=@oldquestionbank
  i just tryed like this  
    
    select * from @questionchoice where  questionid in (select questionid 
    from @question q  where q.questionbank=@oldquestionbank)
    

  
  
  
  

• • Reply
  

  sivaganesh12...

  Member

  227 Points

  308 Posts

  Re: how to make a copy of row in a table in that table itself by passing one parameter @oldque...

  Nov 17, 2012 04:56 AM|LINK

  right now iam trying a output like this

  questionid questionbank indexnumber question crdate
  1 B7152F93-45CE-4485-9672-7E0E5F62F775 1 q1 2012-11-11 10:23:23.910
  2 B7152F93-45CE-4485-9672-7E0E5F62F775 2 q2 2012-11-11 10:23:23.910
  3 B001F18B-326B-4438-AD04-A5192D601053 1 q1 2012-11-17 11:04:04.870
  4 B001F18B-326B-4438-AD04-A5192D601053 2 q2 2012-11-17 11:04:04.870
  
  
  
  choiceid questionid indexnumber choice isactive
    1     1   1     A    1
    2    1   2     b     1
    3    1   3     c    1
    4    2   1     a    1
    5   2   2     b     1
    6   3   1        A    1
    7   3   2        b    1
    8   3   3     c    1
    9   4   1     a    1
   10   4   2     b    1

• • Reply
  

  sandeepmitta...

  Contributor

  6767 Points

  1057 Posts

  Re: how to make a copy of row in a table in that table itself by passing one parameter @oldque...

  Nov 17, 2012 06:44 AM|LINK

  insert into @questionchoice
  select q2.questionid, c.indexnumber, c.choice, c.isactive
  from (
  	select questionid, indexnumber
  	from @question
  	where questionbank = @oldquestionbank
  ) q1 inner join (
  	select questionid, indexnumber
  	from @question
  	where questionbank = @questionbank
  ) q2 on  q1.indexnumber = q2.indexnumber
  inner join @questionchoice c on q1.questionid = c.questionid

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Nov 17, 2012 06:56 AM
  • Marked as answer by sivaganesh1234 on Nov 17, 2012 06:58 AM