Help me with this SQL: 'DO THIS for ALL ROWS in TABLE'

Question

[using SQL Server 2005]

I have a table full of users, I want to assign every single user in the table (16,000+) to a course by creating a new entry in the assignment table as well as a new entry in the course tracking table so their data can be tracked. The problem is I do no know how to do a loop in SQL because I don't think you can but there has got to be a way to do this...

FOR EACH user in TABLE write a row to each of the two tables with userID from user TABLE...

how would I do this? please help!

Answer 1

You'd do this with 2 insert statements. You'd want to wrap this with a transaction to ensure consistency, and may want to double-check our isolation level to make sure that you get a consistent read from the users table between the 2 queries (take a look at SNAPSHOT or SERIALIZABLE to avoid phantom reads).

BEGIN TRAN

INSERT  Courses
        (UserID, CourseNumber, ...)
SELECT  UserID, 'YourCourseNumberHere', ...
FROM    Users

INSERT  Assignments
        (UserID, AssignmentNumber, ...)
SELECT  UserID, 'YourAssignmentNumberHere', ...
FROM    Users

COMMIT TRAN   

Answer 2

Something like:

insert into CourseAssignment (CourseId, StudentId)
select 1 -- whatever the course number is
   , StudendId
from Student