CODEWITHSUNDEEP
sqlsql-server
Rodrigo Souza
Rodrigo Souza

Reputation: 7332

How to create a loop for each row in SQL?

I have 3 tables (Users, CourseUsers and Courses which represent the courses assigned to a single user).

I would like to create a procedure which, as soon as I create a new course, will assign this course to every user. What I see I need here is a For each loop to iterate through every user inserting a new row in CourseUsers table

How do I create a loop in SQL?

Upvotes: 0

Views: 3590

Answers (4)

ipr101
ipr101

Reputation: 24236

Try - 

INSERT INTO CourseUsers (CourseID,UserID)
SELECT 7, UserID FROM Users

Where 7 is your course id (this would obviously change from course to course!).

Upvotes: 1

Oded
Oded

Reputation: 498914

You don't need a loop for doing this.

You can use an INSERT trigger and access the INSERTED logical table, operating on the results of that.

Upvotes: 1

Hans Kesting
Hans Kesting

Reputation: 39274

In this case, you don't.

You can issue an insert statement that will insert a new row in that CourseUsers table for every User:

INSERT INTO CourseUsers(CourseId, UserId)
SELECT @CourseId, UserId
FROM Users

SQL is a set-based language: usually you can work on an entire set of data, without the need for loops.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 452978

You don't need a loop.

DECLARE @CourseId INT;

INSERT INTO Courses(Name) VALUES('Some Course');

SET @CourseId  = SCOPE_IDENTITY();

INSERT INTO CourseUsers(UserId, CourseId)
SELECT UserId, @CourseId
FROM Users;

Upvotes: 6

Related Questions