Creating a table and doing multiple loops to add more rows

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 2000 Forums

SQL Server Development (2000)

Creating a table and doing multiple loops to add more rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-07 : 08:52:27

correak writes "Hi,

I'm completely new to MS SQL, but have been asked to create a stored procedure that will be called from ColdFusion. The stored procedure must return one big table to ColdFusion, which I will then use to dynamically output an Excel spreadsheet using XML.

My question is about the stored procedure part of this. I have to get a list of all students and their info (lname, fname, gender, email etc.)that are enrolled in each of the 3 degree programs. Then I need to loop through each student id in that recordset and get info about schools they atended (which can be more than one record for each student). I also need to loop through each student ID and get their scores (which again can be more than record per student id).

A friend suggested creating using either a temporary table or a table variable. I'm not too sure which one will help me in this case since I will need to have around 15-20 columns in the table.

This is how far I have gotten so far. I created a temp table #admTable, and did
Select (SAGPID, lname, fname, email, gender)
Into #admTable
From ...
Where..
GO

Now, how can i loop through this temp table and get school information for each student from another table, say, institution, such that institution.sagp = sagpid of the student in the table. I also need to add these rows to the same table without losing any information previously gathered.

Sorry if this sounds really confusing, but I tried explaining it as clearly as I could.

Thanks to anyone that replies :)"

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-07 : 09:04:31

I think you could use a change in mentality from thinking programmatically about looping through things. You shouldn't need to "loop" at all to get the info that you want. You should just be able to do one query that joins all the relevant tables together.

-------
Moo. :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 09:13:57

there is no need for temp table or anything else.
this should help u get on zour way:


select 	t1.*, t2.*, t3.*
from 	Student t1
		left join Institution t2 on (t1.sagp = t2.sagp)
		left join Scores t3 on (t1.StudentId = t3.StudentId)
Where 	(conditions)

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources