February 4, 2012 at 8:29 am
Thank you in advance
I need to reserve memory and keep performance the best I am using declare @mytable as table (xxxx)
Is there a better way please see example below
in my stored procedure. Example I have a table
section (sectionid, sectionname, parentsection)--if parentsection=0 this means that this section is the parent --if parentsection=1 for example that means section with sectionid=1 is the parent of this current record.
students can be enrolled in either child or parent but all should show in parent and all should be added to the parent assessment the child do not have assessments
enrollment (studentid,sectionid,enrollmentstatus)
assessment (sectionid,assessmentid,assessmentname,maxpoint)
assessmentpoint(assessmentid,studentid,score)
all students enrolled in either section or child section should be included in the assessments of the parent the child section has no assessment and on adding a new assessment I need to pull all students enrolled either in section or children section add a record for each in the assessmentpoint with a point zero and if a new student is enrolled I add a record for him for all assessments in the section that he is enrolled
example of using temporary tables
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);
February 4, 2012 at 9:39 am
Also asked here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170990
Also, exactly what is your problem here? Does the query not work? Is the performance unacceptable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 6:52 am
To get a full reference here
February 6, 2012 at 7:30 am
You might want to read this and then decide which method is best for you situation
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply