insert into table based on contents of 2 others

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert into table based on contents of 2 others

Author

Topic

ravensensei
Starting Member

13 Posts

Posted - 2009-11-23 : 14:39:35

I have a table of users. I have a table of weekending dates. I have a main table that I would like to insert a row into based on the other two tables.

What I'd like to do is write a query that will insert 1 row for each user for each of the 52 weeks in the weekending table into the main table.

I wrote something like this do to each user indivudually. I just change the dnnID each time to insert a new person's 52 weeks.
-----------------------
declare @dnnID int
set @dnnID = 127 /* this is their userid in the WeeklyScheduleUser table */

insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',94)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',95)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',96)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',97)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',98)
.....
------------------------
they're is 52 of the insert statements and I just change the dnnID each time. Needless to say, it's a lot of inserts. I'd really like to find a way to write something that would look at the user and insert a new row for each of the 52 weeks of the year into the main table.

I had thought to use for each, but I'm unsure how to get started.

while(select seq from weeklyscheduleUser)<128 (127 is the last sequence number in that table)
begin
while(select seq from weeklyscheduleSaturdays) < 146 (146 is the first full week of 2010)
begin
INSERT INTO WeeklySchedule (userID, day1, day2, day3, day4, day5, weekending) values (<want to use weeklyscheduleUser.seq here>, 'In Building' ,'In Building' ,'In Building' ,'In Building' ,'In Building' ,<want to use weeklyscheduleSaturday.seq here>)
break
continue
end

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-11-23 : 17:01:35

This will give you what you asked for, but I would definantly think about starting from scratch and designing a better table layout.



SELECT TOP 127 IDENTITY(INT,1,1) AS N
INTO #Numbers
FROM 
sysobjects a 
cross join
sysobjects b
cross join
sysobjects c



insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) 
select a.n, 'Unknown','Unknown','Unknown','Unknown','Unknown',b.n + 93
from
#Numbers a
Cross Join
#Numbers b
where 
a.N <= 127
and
b.N <=52
order by a.n


drop table #Numbers

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

ravensensei
Starting Member

13 Posts

Posted - 2009-11-24 : 07:01:22

What would you suggest as a table layout? I designed this literally in a day or two to satisfy someone for a project. I have no problem doing it correctly.

The task is to allow someone to change their own schedule for the week, letting people know where they are going to be Mon - Fri.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-11-24 : 15:19:09

First I would use a datetime column for Weekending this way there is no confusion what week/date it is refering too. You can have a column that is the datediff between the current week and your starting week as well, but this way there is no confusion.

Second I would try to allow for some scalability (userID, weekday as int, day as a datetime,WeekEnding,Description varchar(600)), and I would not do a day1-day5. This will allow for scalability and make it easy to query against, and you could always add weekends on as well.

Third, normalize the data as much as possible. I am not sure what you are using this for, but you can build onto this as much as you want, just try to keep the data normalized for quick querying.

By using that scenerio, you can now query the data much easier if you ever need to do complex queries, without needing to factor in 5 differant columns.

I hope this helps.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources