Looping through a result set in stored procedure

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)

Looping through a result set in stored procedure

Author

Topic

Skydolphin
Starting Member

43 Posts

Posted - 2003-07-11 : 13:32:08

Hi All,

I am trying to write a stored procedure that runs a simple select statement, gets the values of certain fields in the table and inserts them along with some other fields into a new table. The queries run but the insert only happens for the last record returned from the select. How can I loop through the results inserting a record for each one?

All of the other fields are either set using a declare or are passed in the procedure.

BEGIN
Select @strBarcode = Barcode, @strLID = LID from TableName

Insert into newTable(Drawing_Type, Barcode, Sheet_Dim, Page, Description, Date, LID, Notes, SCovr, SIndx, SPlan, SProf, SDet, SNote, Station_No, Original_Page, Category_No, Dwg_No, Type, FNew, FRest, FPark, FExist) values
(@strDrawing_Type, @strBarcode, @strSheet_Dim, @strPage, @strDescription, @strDate, @strLID, @strNotes, @strSCovr, @strSIndx, @strSPlan, @strSProf, @strSDet, @strSNote, @strStation_No, @strOriginal_Page, @strCategory_No, @strDwg_No, @strType, @strFNew, @strFRest, @strFPark, @strFExist)
END

Any ideas would be greatly appreciated.

Thanks

Rhonda

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 13:40:51

Just combine the INSERT and your SELECT:


Insert into newTable(
	  Drawing_Type, Barcode, Sheet_Dim, Page
	, Description, Date -- Reserver words..you should change them
	, LID, Notes, SCovr, SIndx, SPlan, SProf, SDet
	, SNote, Station_No, Original_Page, Category_No
	, Dwg_No, Type, FNew, FRest, FPark, FExist)
SELECT  
	  @strDrawing_Type
	, Barcode -- column from your table
	, @strSheet_Dim, @strPage, @strDescription
	, @strDate
	, LID -- same here
	, @strNotes, @strSCovr, @strSIndx
	, @strSPlan, @strSProf, @strSDet, @strSNote
	, @strStation_No, @strOriginal_Page, @strCategory_No
	, @strDwg_No, @strType, @strFNew, @strFRest, @strFPark, @strFExist
FROM TableName