Insert Into Select? - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Insert Into Select?

Author

Topic

DaNuGai
Starting Member

10 Posts

Posted - 2013-03-27 : 10:00:53

Hi Guys,

I have a table with the following data: RoomID, BuildingID, MaxSeats, and Room Availability (5 columns, Mon-Fri, boolean fields).

I have another table with the list of employees and the days they are available. EmployeeID, ..., EmployeeAvailability (5 columns, Mon-Fri, boolean fields).

Now, in the third table, I'm looking to randomly assign (insert records) where Employee availability matches the Room availability, but also taking into consideration the maximum number of seats allowed in a given room.

Any ideas on how I can go about doing this in SQL? I know I can loop through the employee table, match the Emp Availability to Room Availability, and count the #OfEmp in that room before doign an insert. However, I don't know if that's the most efficient way of accomplishing this task. I'm hoping someone here can guide/help me accomplish this task in more of an efficient manner if possible.

Thanks in advance.

DaNuGai
Starting Member

10 Posts

Posted - 2013-03-29 : 08:10:11

Morning Guys,

Is there anyone here who could help me with this? I'd appreciate your help.

Thanks in advance.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 08:18:43

Post the structure of 3rd table, sample data for first two tables and output for 3rd table based on your logic...
Its better to get quick response from anyone in the forum

DaNuGai
Starting Member

10 Posts

Posted - 2013-03-29 : 08:43:35

Hi Bandi, I appreciate your reply. Here is the same table structure...


CREATE TABLE Participant (
	ParticipantID INT NOT NULL,
	ParticipantName VARCHAR(50) NOT NULL,
	BuildingID SMALLINT NOT NULL,
	AvailableDayA BIT NOT NULL,
	AvailableDayB BIT NOT NULL,
	AvailableDayC BIT NOT NULL,
	AvailableDayD BIT NOT NULL,
	AvailableDayE BIT NOT NULL,
   PRIMARY KEY(ParticipantID)
)
CREATE TABLE BuildingRoom (
	BuildingID SMALLINT NOT NULL,
	RoomNum VARCHAR(5) NOT NULL,
	TrainerID INT NOT NULL,
	MaxSeats SMALLINT NOT NULL,
	AvailableDayA BIT NOT NULL,
	AvailableDayB BIT NOT NULL,
	AvailableDayC BIT NOT NULL,
	AvailableDayD BIT NOT NULL,
	AvailableDayE BIT NOT NULL,
   PRIMARY KEY(BuildingID, RoomNum)
)
CREATE TABLE ParticipantEnrollment (
	ParticipantID INT NOT NULL,
	BuildingID SMALLINT NOT NULL,
	RoomNum VARCHAR(5) NOT NULL,
   PRIMARY KEY(ParticipantID, BuildingID, RoomNum)
)

Just so you know, I'm looking to auto populate ParticiantEnrollment table with the number of participants not exceeding the max seats limit

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 05:54:00

May be this?


--INSERT INTO ParticipantEnrollment
SELECT ParticipantID, BuildingID, RoomNum
FROM (SELECT p.ParticipantID, p.BuildingID, b.RoomNum, COUNT(*) OVER(PARTITION BY p.ParticipantID, p.BuildingID, b.RoomNum) Cnt, MaxSeats
		FROM Participant p
		JOIN BuildingRoom b
		ON p.BuildingID = b.BuildingID AND CAST(RAND()*ParticipantID as INT) < MaxSeats
		WHERE( p.AvailableDayA = 1 OR p.AvailableDayB = 1 OR  p.AvailableDayC= 1 OR  p.AvailableDayD= 1 OR  p.AvailableDayE= 1 )
		AND ( b.AvailableDayA= 1 OR b.AvailableDayB= 1 OR b.AvailableDayC= 1 OR b.AvailableDayD= 1 OR b.AvailableDayE =1)
	)t
WHERE t.Cnt < = MaxSeats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources