can any one help me to insert a solution of a query of a table insert into another table RSS

• • Reply
  

  sivaganesh12...

  Member

  228 Points

  309 Posts

  can any one help me to insert a solution of a query of a table insert into another table

  Feb 21, 2012 10:53 AM|LINK

  
  SELECT
  DISTINCT( es.scheduledexamsid)
  ,qp.questionpaperid
  ,a.memberid
  ,(SELECT sum(CASE WHEN [MemberAnswerKey]= AnswerKey THEN MarksPerQuestion ELSE 0 END)
  FROM
  dbo.tblScheduledExams es
  inner JOIN dbo.tblQuestionPaper qp on (qp.QuestionPaperID = es.QuestionPaperID)
  inner JOIN dbo.tblAnswer a on (es.ScheduledExamsID = a.ScheduledExamsID)
  inner JOIN dbo.tblQuestions qs on (qs.questionid = a.questionid)
  WHERE
  a.memberid=1 AND es.scheduledexamsid=1 AND qp.QuestionPaperID=1)
  ,(SELECT sum(CASE WHEN [MemberAnswerKey]= AnswerKey THEN MarksPerQuestion ELSE 0 END)/CAST(sum(marksPerQuestion) as float)*100
  FROM
  dbo.tblScheduledExams es
  inner JOIN dbo.tblQuestionPaper qp on (qp.QuestionPaperID = es.QuestionPaperID)
  inner JOIN dbo.tblAnswer a on (es.ScheduledExamsID = a.ScheduledExamsID)
  inner JOIN dbo.tblQuestions qs on (qs.questionid = a.questionid)
  WHERE
  a.memberid=1 AND es.scheduledexamsid=1 AND qp.QuestionPaperID=1)
  FROM
  dbo.tblScheduledExams es
  inner JOIN dbo.tblQuestionPaper qp on (qp.QuestionPaperID = es.QuestionPaperID)
  inner JOIN dbo.tblAnswer a on (es.ScheduledExamsID = a.ScheduledExamsID)
  inner JOIN dbo.tblQuestions qs on (qs.questionid = a.questionid)
  WHERE
  a.memberid=@memberid AND
  es.scheduledexamsid=@scheduledexamsid
  AND
  qp.questionpaperid=@questionpaperid

  THE OUTPUT IS LIKE THIS

  scheduledexamsid     questionpaperid      memberid        (No column name)        (No column name)
  1                                   1                      1                        10                             100

  (AND NOW  I  JUST  WANT TO INSERT  OF THE  STORE PROCEDURE  TO BE INSERTED  IN A ANOTHER  TABLE   dbo.tblResult)

  THE  TABLE  WILL LOOK LIKE THIS

  resultid(pk)     ScheduledExamsId (fK)    QuestionPaperId (FK)     memberid(fk)         score        percentage

  AND I NEED TO  OUTPUT OF THE STOREPROCEDURE  TO BE INSERTED IN dbo.tblResult (resultid(pk) IS A IDENTITITYCOLUMN AND PRIMARYKEY)

  resultid(pk)     ScheduledExamsId (fK)    QuestionPaperId (FK)   memberid(fk)     score           percentage

           1                                   1                                    1                         1                     10                    100

  IN THAT SAME STORE PROCEDURE  I WANT TO WRITE  INSERT QUERY FOR  dbo.tblResult

  • Edited by sivaganesh1234 on Feb 21, 2012 10:55 AM
  • Edited by sivaganesh1234 on Feb 21, 2012 10:57 AM
  • Edited by sivaganesh1234 on Feb 21, 2012 10:59 AM
  • Edited by sivaganesh1234 on Feb 21, 2012 10:59 AM
  • Edited by sivaganesh1234 on Feb 21, 2012 11:00 AM
• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: can any one help me to insert a solution of a query of a table insert into another table

  Feb 21, 2012 11:06 AM|LINK

  sivaganesh1234

  IN THAT SAME STORE PROCEDURE  I WANT TO WRITE  INSERT QUERY FOR  dbo.tblResult

  Use Insert into select statement to insert records from select statement..

  insert into dbo.tblResult
  (
  	ScheduledExamsId,QuestionPaperId,memberid,score
  )
  SELECT DISTINCT( es.scheduledexamsid),
  qp.questionpaperid,a.memberid,
  (SELECT sum(CASE WHEN [MemberAnswerKey]= AnswerKey THEN MarksPerQuestion ELSE 0 END) AS Data
  FROM  dbo.tblScheduledExams es
  inner JOIN dbo.tblQuestionPaper qp on (qp.QuestionPaperID = es.QuestionPaperID)
  inner JOIN dbo.tblAnswer a on (es.ScheduledExamsID = a.ScheduledExamsID)
  inner JOIN dbo.tblQuestions qs on (qs.questionid = a.questionid)
  WHERE a.memberid=1 AND es.scheduledexamsid=1 AND qp.QuestionPaperID=1)
  
  DECLARE @ID INT
  SELECT @ID=SCOPE_IDENTITY()
  
  DECLARE @Percentage DECIMAL(18,2)
  
  SELECT @Percentage=sum(CASE WHEN [MemberAnswerKey]= AnswerKey THEN MarksPerQuestion ELSE 0 END)/CAST(sum(marksPerQuestion) as float)*100
  FROM dbo.tblScheduledExams es
  inner JOIN dbo.tblQuestionPaper qp on (qp.QuestionPaperID = es.QuestionPaperID)
  inner JOIN dbo.tblAnswer a on (es.ScheduledExamsID = a.ScheduledExamsID)
  inner JOIN dbo.tblQuestions qs on (qs.questionid = a.questionid)
  WHERE a.memberid=1 AND es.scheduledexamsid=1 AND qp.QuestionPaperID=1)
  FROM dbo.tblScheduledExams es
  inner JOIN dbo.tblQuestionPaper qp on (qp.QuestionPaperID = es.QuestionPaperID)
  inner JOIN dbo.tblAnswer a on (es.ScheduledExamsID = a.ScheduledExamsID)
  inner JOIN dbo.tblQuestions qs on (qs.questionid = a.questionid)
  WHERE a.memberid=@memberid
  AND es.scheduledexamsid=@scheduledexamsid
  AND qp.questionpaperid=@questionpaperid
  
  
  UPDATE dbo.tblResult SET percentage=@Percentage WHERE resultid=@ID

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  sivaganesh12...

  Member

  228 Points

  309 Posts

  Re: can any one help me to insert a solution of a query of a table insert into another table

  Feb 22, 2012 05:43 AM|LINK

  iam not  getting  correct ouput

• • Reply
  

  yrb.yogi

  Star

  14460 Points

  2402 Posts

  Re: can any one help me to insert a solution of a query of a table insert into another table

  Feb 22, 2012 06:06 AM|LINK

  sivaganesh1234

  iam not  getting  correct ouput

  Check your select query??

  Is your select query result is okay?

  Is resultid column of dbo.TestResult table is Identity column? if yes than above solution work.

  If not than post your problem with some data with table structure.

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  Chen Yu - MS...

  All-Star

  21829 Points

  2513 Posts

  Microsoft

  Re: can any one help me to insert a solution of a query of a table insert into another table

  Feb 27, 2012 07:52 AM|LINK

  sivaganesh1234

  iam not  getting  correct ouput

  Hi,

  Did you get the correct output after you check what @yrb.yogi said? If your problem still here, please post the detail information about your problem to provide us more information.

  Thanks.

  Please mark the replies as answers if they help or unmark if not.
  Feedback to us
  
  Develop and promote your apps in Windows Store