SQL query to insert stats of students to a table-CodePudding

I would like some help on how to write an sql server query in order to insert the monthly stats of students into a table.

Now I have another table with the Students Details like StudentID, name, etc. Also multiple other tables with grades, presence etc.

My goal is to select all studentsIDs from StudentDetails and insert them to the Monthly Stats table while I calculate Grade1, Grade2, and Absences from other multiple tables.

What is the best way to write such a query?

Do I first insert the StudentsIds, Year column and Month column with a select into query and after that, I iterate somehow through every studentid that were inserted and run update queries (for calculating rest of columns) for every studentID for the specified month and year?

I just need an example or some logic on how to achieve this. For the the first part of inserting studentids I have this:

declare @maindate date = '20230101';

insert into Monthly_Stats (StudentID, Year, Month)
(select StudentID, AllocatedYear, AllocatedMonth
from Students_Allocation
where AllocatedMonth = DATEPART(MONTH, @maindate)
and AllocatedYear = DATEPART(YEAR, @maindate)
and Active = 1)

After insertion I would like somehow to update every other column (Grade1, Grade2,Absences...) from multiple other tables for each StudentID for the aforementioned Month and Year.

Any ideas?

CodePudding user response：

This is what I usually perform batch update

UPDATE Monthly_Stats  
SET
       Monthly_Stats.GRADE1 = T1.Somedata   T2.Somedata    T3.Somedata  
FROM
       Monthly_Stats MS INNER JOIN TABLE_1 as T1        
left join TABLE_2 as T2 on T1.StudentID = T2.StudentID and T1.Year = T2.Year and T1.Month = T2.Month        
left join TABLE_3 as T3 on T1.StudentID = T3.StudentID and T1.Year = T3.Year and T1.Month = T3.Month 
ON 
       MS.StudentID = T1.StudentID and MS.Year = T1.Year and MS.Month = T1.Month;

Be careful with the two left join. Depending on your database normalization, you may need more conditions in the ON clause to ensure the join output is as expected.

Hope it helps