June 20, 2008 at 11:42 am
I have a table with 99 users. I need a row for each week added into another table for each of the 99 users.
what I'd like to do is something along the lines of getting the userid from table 1, putting it in for @userid variable, and getting weekNumber from table 2 and put that in for @weekending, and run the following statement.
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekending) values(@userID, 'unknown', 'unknown', 'unknown', 'unknown', 'unknown', @weekending)
I figured I'd could do something like:
for i <= the last userID
select userid from weeklyscheduleuser and set it = @userid
but then how do I get the next value out of the table with the week numbers?
I can also start with i = 1 and i++ until it gets to 99 which is the last user id and do something similar with the weekending id. j = 1 and j++ it until it gets to the last row (which I know is 120)
I guess what I need to know is how to put an insert statement inside a while clause, right?
while i < lastID and j < lastWeekNumber
insert blah blah blah
i++
j++
June 20, 2008 at 12:20 pm
so I started playing with cursors, and it's getting me closer, but not quite.
How do I do a cursor within a cursor? this works fine, but the user_cursor is only fired once.
declare @user int
declare @week int
declare week_cursor cursor for
select seq from weeklyscheduleSaturdays
where seq > 66
open week_cursor;
fetch next from week_cursor
into @week;
declare user_cursor cursor for
select seq from weeklyscheduleUser
open user_cursor;
fetch next from user_cursor
into @user;
while @@Fetch_status = 0
Begin
print @week
print @user
fetch next from week_cursor
into @week;
end;
close user_cursor;
deallocate user_cursor
close week_cursor;
deallocate week_cursor
June 20, 2008 at 12:39 pm
Can't you simplify the whole thing by using a select with a join between the two tables?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 12:56 pm
I think that is the ticket.
It sounds to me like you need a CROSS JOIN
June 20, 2008 at 12:59 pm
For example:
CREATE TABLE #Users (UserName VARCHAR(10))
INSERT #Users VALUES ('Tom')
INSERT #Users VALUES ('Joe')
INSERT #Users VALUES ('Amy')
CREATE TABLE #Dates (MyDate DATETIME)
INSERT #Dates VALUES ('1/1/2001')
INSERT #Dates VALUES ('1/1/2002')
INSERT #Dates VALUES ('1/1/2003')
INSERT #Dates VALUES ('1/1/2004')
INSERT #Dates VALUES ('1/1/2005')
SELECT
*
FROM
#Users
CROSS JOIN #Dates
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply