Insert - Loop??

[MartinaL]

I need to insert some records into a table for each record in another table.

I have a table called Courses (tbl_Course), these course have Catogories (tbl_CourseCategories) and each category can have multiple Competencies (tbl_Competencies).

Now I want a stored procedure which runs and creates and entry in a table called CadetCourseCompetencies for each of the competencies in a selected course.

So the CadetID and the CourseID are sent in.

THis is what I have and it does insert 1 row for the first competency in the first category in the selected course, but I need it to loop through and add a row for each competency based on the CourseID sent in.

This is what I have so far;

CREATE Procedure usp_cadetscourse_enrol
( 
 @CadetID int,
 @CourseID int,
 @EditedBy int
)
AS
INSERT INTO tbl_CadetsCourseCompetency
( 
 CadetID, 
 CompID, 
 CreationDate,
 LastEdited,
 EditedBy
)
SELECT 
 @CadetID, 
 comp.CompID, 
 getdate(),
 getDate(),
 @EditedBy
 
FROM tbl_CourseCompetency comp
, tbl_CourseCategory cat
WHERE cat.CourseID  = @CourseID AND comp.CatID = cat.CatID
GO

 

[fpineda101]

I know you are using SQL Server, but the solution in Oracle would be something like this...and hopefully SQL Server has the same capabilities.

Declare a cursor by selecting specific records by a key. This creates an array in PL/SQL (even though its called a cursor). Then you loop through each element in the cursor and inserting into the new table a record selected from the old with the current cursor element as the key.

You can also ask if this is possible in a SQL forum.