Geek Answers Handbook

Help me with this SQL: "DO IT FOR ALL ROWS IN TABLE"

[using SQL Server 2005]

I have a table full of users, I want to assign each individual user in the table (16,000+) to the course, creating a new record in the assignment table, as well as a new record in the course tracking table so that their data can be tracked. The problem is that I don’t know how to do a loop in SQL, because I don’t think you can, but there is a way to do it ...

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

how would i do that? please, help!

+4
source share
7 answers

Sort of:

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

You would do this with two insert statements. You want to wrap this with a transaction to ensure consistency and you might want to double check our isolation level to make sure you get a consistent read from the user table between the two queries (look at SNAPSHOT or SERIALIZABLE to avoid phantom reading).

 BEGIN TRAN INSERT Courses (UserID, CourseNumber, ...) SELECT UserID, 'YourCourseNumberHere', ... FROM Users INSERT Assignments (UserID, AssignmentNumber, ...) SELECT UserID, 'YourAssignmentNumberHere', ... FROM Users COMMIT TRAN
+13
source

something like this, no need to loop, if you have duplicates using different ones also change the value 1 with the course value

 insert into AssingmentTable select userid,1 from UserTable insert into OtherTable select userid,1 from UserTable
+1
source

Maybe I misunderstand your question, but I think you need an INSERT..SELECT statement

 INSERT INTO TABLE2 SELECT filed1, field2 field3 from TABLE1
0
source

SQL is working on sets. It does not require cycles.

what you are looking for may be a paste in command.

 INSERT INTO <new_table> (<list of fields, comma separated>) SELECT <list of fields,comma separated> FROM <usertable> WHERE <selection condition if needed>
0
source 
 --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;
0
source

I would do this using a variety of grounded approaches that many others have already published ...

... however, just for completeness, it's worth noting that you could do a loop if you want. Look at cursors and while loops in books online to see some examples.

Just please don't fall into the trap of using cursors like so many newbies. They have their uses, but if used incorrectly, they can be terrible - almost always the best way to do things.

0
source

More articles:


All Articles