May 22, 2006 at 4:32 am
I have a query which uses 3 cusror to update a database table.
The structure is as below:
Input Table Structure:
Student Id Course Date of Enrollment Date_of_Completion
s1
s1
s1
s2
s3
s4
s4
Cursor 1:
Select distinct Student ID from the Input Set.
Cursor 2:
Select all records from the input Set for a single Student Id
Cursor 3:
Select for each Student Id & Course ID, the detail of lecture attended (from a second table Coarse Lecture.
--Update a Third table on meeting certain condition the values from the Input Set Table for a given Student Id & Course Id.
-----------------------
1. Is there any other otpion where I can eliminate the user of cursor.
2. Can I Merge the First 2 or last 2 Cusrosr and then use the loop to validate & Update.
3. I want to know the performance issue with above approach and any other approach.
Thanks
May 22, 2006 at 5:08 am
Can you give the structure of the 2nd and 3rd tables, an example of input data and an example of your desired output please.
There's almost certainly a set-based solution for your problem. In virtually all cases, a set-based solution will out-perform a cursor-based solution.
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
May 22, 2006 at 6:35 am
Input Table
| StdId | CSID | DOENT | DOC |
| s1 | c1 | some date | some date |
| s1 | c2 | some date | some date |
| s1 | c3 | some date | some date |
| s2 | c2 | some date | some date |
| s2 | c3 | some date | some date |
| s3 | c1 | some date | some date |
| s4 | c1 | some date | some date |
| s4 | c2 | some date | some date |
Second Table
| STDID | CSID | LectureID | Date & Time |
| s1 | c1 | l1 | some date & Time |
| s1 | c2 | l1 | some date & Time |
| s1 | c3 | l2 | some date & Time |
| s3 | c1 | l3 | some date & Time |
Depending on certain condition from Second Table only valid entries will insert to the 3rd table from the given 1st table
Third Table
| STDID | CSID | DOENT | DOC |
| s1 | c1 | some date | some date |
| s1 | c2 | some date | some date |
| s1 | c3 | some date | some date |
May 22, 2006 at 6:47 am
Doesn't looks too hard. One clarification please. Why didn't
| s3 | c1 | some date | some date |
appear in the thrid table?
What are the 'certain conditions' from second table?
first pass solution:
INSERT INTO ThirdTable (Stdid, CSID, Doent, doc)
SELECT FirstTable.Stdid, FirstTable.CSID, FirstTable.Doent, FirstTable.doc
FROM FirstTable INNER JOIN SecondTable ON FirstTable.Stdid = SecondTable.Stdid AND FirstTable.CSID = SecondTable.CSID
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply