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

Something like:

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

Answer 2

something like this, no need for looping, if you have dups use distinct also change 1 with the course value

insert into AssingmentTable
select userid,1
from UserTable

insert into OtherTable
select userid,1
from UserTable

Answer 3

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 4

maybe I misuderstand your question, but I think you need INSERT..SELECT statement

INSERT INTO TABLE2
SELECT filed1, field2 field3 from TABLE1

Answer 5

SQL works on sets. It doesn't require loops ..

what you are looking for might be the "insert into" command.

INSERT INTO <new_table> (<list of fields, comma separated>)
SELECT <list of fields,comma separated>
FROM <usertable>
WHERE <selection condition if needed>

Answer 6

--grab 1 record for each student, and push it into the courses table
--i am using a sub-select to look up a course id based on a name
--that may not work for your situation, but then again, it may...
INSERT INTO COURSES(
  COURSE_ID
 ,STUDENT_ID
)
SELECT
  (SELECT COURSE_ID FROM COURSES WHERE COURSE_NAME = 'MATH')
 ,STUDENT_ID
FROM
  STUDENTS;

--grab your recently entered course data and create an entry in
--your log table too
INSERT INTO COURSE_DATA(
  COURSE_ID
 ,STUDENT_ID
)
SELECT
  COURSE_ID
 ,STUDENT_ID
FROM
  COURSES;

Answer 7

I would do this using the set based approaches that lots of others have already posted...

...however, just for completeness it is worth noting that you could do a loop if you really wanted to. Look up cursors and while loops in books online to see some examples.

Just please don't fall in to the trap of using cursors as lots of newbies do. They have their uses but if they're used incorrectly they can be terrible - there's almost always a better way of doing things.