subquery vs declare @mytable as table

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

SQL Server 2008 Forums

Transact-SQL (2008)

subquery vs declare @mytable as table

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 10:19:42

Hi All,
I would like to do the following using sub queries
I want to merge studentid's of all students enrolled either in parent or child section and all assessment id's for all assessments of the primary section and scores to the assessmentpoint table.
If the student already has a score do not touch it if not add a row with null value.

here are my tables
section (sectionid, sectionname, parentsection)
enrollment (studentid,sectionid,enrollmentstatus)
assessment (sectionid,assessmentid,assessmentname,maxpoint)
assessmentpoint(assessmentid,studentid,score)
Note parentsection field is zero if the section is the parent
other wise it will be the sectionid of the parent section
I used before @declare @temp tables but this was taking memory
Is subqueries better and how to do that using a subquery.

Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 10:30:47

can you show the sample output you require out of the tables above?
I feel like you need to make use of recursive CTE for getting the parent child relationships from section table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 10:37:19

thank you.
here is my stored procedure to do the above
I do not have an output I am just updating the assessmentpoint table
because I get enrolled student from xml every day and I update the enrollment table so I need also to update the assessmentpoint table
I am using declare @temptable a lot in my stored procedures I found that the sql memory usage is high so I am trying to improve it

I will send you another one that produces an output
declare @tempenrollment table
(sectionid int,studentid int,parentsection int)

insert into @tempenrollment
(sectionid,studentid,parentsection)
(select
S.sectionid,E.studentid,S.parentsection
from sp.section s
inner join
sp.sectionsemester ss
on
s.sectionid=ss.sectionid
inner join
enrollment.enrollment e
on
e.sectionid=s.sectionid
where semesterid =@semesterid
and enrollmentstatus=1)

update @tempenrollment
set sectionid=parentsection
where parentsection !=0

declare @TempAssessmentPoint table
(AssessmentID int,studentid int,Points decimal,updatedby int)
insert into
@TempAssessmentPoint
(AssessmentID,studentid,Points,updatedby)
(select AssessmentID,StudentID ,Null,1
from GradeBook.Assessment A
inner join
@tempenrollment E
on
A.SectionID=E.SectionID)

merge into GradeBook.AssessmentPoint T

USING @TempAssessmentPoint S
ON (T.AssessmentID=S.AssessmentID
and
T.StudentID=S.StudentID)

WHEN NOT MATCHED
THEN INSERT (AssessmentID,StudentID,Points,Updatedby)

VALUES (S.AssessmentID,S.StudentID,S.Points,S.Updatedby);

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 10:42:03

sorry your procedure doent make much sense

In merge you're trying to insert AssessmentID,StudentID,Points,Updatedby into GradeBook.AssessmentPoint table whereas as per definition it has only below columns
assessmentid,studentid,score

so I'm not sure how this is going to work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 10:43:39

sorry I forgot to add the column update in my post but it is in my stored procedure and it is working.
I was trying to abbreviate

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 10:47:33

can section parent child relationship continue to multiple levels? like parent section,its child, chil's child etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 10:52:33

No it is only one level
The enrollment table is huge and also the assessmentpoint table
I am looking to reserve memory and keep good performanc
thank you so so much

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 11:01:18

I forgot to mention

there is another table because we might have seen sectionsemester in the stored procedure
sectionseemester (sectionid,semesterid,sectionstartdate,sectionenddate)
semester(semesterid,semestername,etc)
We did that because we have different types of section
section that have semesters and other public section which don't comply to semester
Thank you again
Hope I did not confuse you
So I am doing the above for all sections in a given semester
i.e the semesterid is a parameter for the first stored procedure

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 11:01:23

[code]
insert into assessmentpoint
select a.assessmentid,e.studentid,null
from enrollment e
inner join section s
on s.sectionid = e.sectionid
inner join assessment a
on a.sectionid = e.sectionid
left join assessmentpoint ap
on ap.assessmentid = a.assessmentid
and ap.studentid = e.studentid
where s.sectionname='primary'
and ap.assessmentid is null
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 11:02:58

quote:
Originally posted by sarahmfr

I forgot to mention
there is another table because we might have seen sectionsemester in the stored procedure
sectionseemester (sectionid,semesterid,sectionstartdate,sectionenddate)
semester(semesterid,semestername,etc)
We did that because we have different types of section
section that have semesters and other public section which don't comply to semester
Thank you again
Hope I did not confuse you

sarah

as per your earlier definition i cant see what relevance it has on your output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 11:07:19

I can not use what you mentioned I am merging
Let me make it simple
If I want to pull all students enrolled in a section and its children if it has any and pull all assessmentid for this section
then use the result of both to do a merge
how can I do that without declaring a temporary tables

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-05 : 11:20:21

sorry i cant guess more
post some sample data and then explain what you want with sample output

see below for guidelines
unless you do that, we will be going in circles

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 11:21:38

Sorry for that.
I will do that.
I am using table variables to do that but it is taking out memory
So is it better to use cte
note the enrollment table and assessment tables are huge
and if cte is better how to do that

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-05 : 11:42:04

First part of the question

section
(sectionid,sectionname,parentsection)
example
(1,first,0)
(2,second,1)
(3,third,0)
this means first has a child second
and third has no children

enrollment
(sectionid,studentid,enrollmentstatus)
(1,1,1)
(1,2,1)
(2,3,1)
(2,4,1)
(3,5,1)

student
(studentid,firstname,lastname)

when i am in section=first
I want to see students enrolled in section=first and its child=second
I want to see the following students
1,2,3,4

second portion
only parent sections have assessments
assessment table (assessmentid,sectionid,maxpoints)
(1,1,10)
(2,2,10)
assessmentpoint table (will have assessmentid,studentid, score)
this will include assessments of the parent and students of both the parent and the children
each student enrolled in either parent or child should have a record in the assessmentpoint table for all assessments of the parent

if a new student is enrolled to either parent or child I need to add a record for him for each assessment of the parent
using merge

Thank you

thanks

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources