SQL Server - 插入多个子查询（3个表）

I have a table called BankHoliday which contains a Date field along with the Id field.. This currently has all of the dates for this year's and next year's Bank Holidays.

我有一个名为BankHoliday的表，其中包含一个Date字段以及Id字段..这个目前包含今年和明年的银行假期的所有日期。

I also have a table called Staff which contains StaffID, StaffName, Active

我还有一个名为Staff的表，其中包含StaffID，StaffName，Active

Now using SQL I am trying to write an insert statement into a table called Calendar in which these Bank Holiday Dates are inserted. This table contains CalendarDate, CalendarID and StaffID,

现在使用SQL我试图将一个插入语句写入一个名为Calendar的表中，其中插入了这些Bank Holiday Dates。该表包含CalendarDate，CalendarID和StaffID，

The StaffID is where my problem is...

StaffID是我的问题所在......

The Question

问题

How can I write an INSERT statement in SQL Server where it will fetch a list of the Active Staff Members along with each individual Bank Holiday Date, and then INSERT them into the Calendar table?

如何在SQL Server中编写INSERT语句，它将获取活动工作人员列表以及每个银行假日日期，然后将它们插入Calendar表中？

Example Data would return:

示例数据将返回：

CalendarID  CalendarDate    StaffID
     1       31/08/2015        1
     2       31/08/2015        2
     3       31/08/2015        3
     4       31/08/2015        4
     5       31/08/2015        5
     6       25/12/2015        1
     7       25/12/2015        2
     8       25/12/2015        3
     9       25/12/2015        4
     10      25/12/2015        5

So to clarify the CalendarDate above will contain a list of BankHoliday Dates that are not currently in this table (WHERE NOT EXISITS)

因此，澄清上面的CalendarDate将包含当前不在此表中的BankHoliday日期列表（WHERE NOT EXISITS）

The StaffID is retrieved from the Staff table where a list contains only Active members

从Staff表中检索StaffID，其中列表仅包含活动成员

2 个解决方案

#1

I think you are looking for a CROSS JOIN.

我想你正在寻找一个CROSS JOIN。

INSERT INTO Calendar 
SELECT  b.CalendarDate, s.StaffID FROM BankHoliday b
CROSS JOIN Staff s
WHERE s.Active = 1

This will get a row for every bank holiday and every active staff member.

这将为每个银行假日和每个活跃的工作人员争取一排。

#2

Ther is an accepted answer already, so I'm to late - never mind. Just one hint: I think you could improve the design:

Ther已经是一个公认的答案，所以我迟到了 - 没关系。只有一个提示：我认为你可以改进设计：

You should create a date-table not only for bank holidays, but a running list of all dates. These dates you can mark as "IsBankHoliday". Such a date list is very usefull in many cases.
您应该创建一个日期表，不仅可以用于银行假期，还可以创建所有日期的运行列表。这些日期您可以标记为“IsBankHoliday”。在许多情况下，这样的日期列表非常有用。
I don't know what you are going to do with this, but normally you would not write data into a physical table, which you can retrieve that easily. So - probably! - it's not the best idea to do this...
我不知道你将如何处理这个问题，但通常你不会将数据写入物理表，你可以轻松地检索它。所以 - 可能！ - 这不是最好的主意......

Just my solution to show you the connection of the tables. In fact this is nothing else then the other solution with CROSS JOIN... Person4 is not active, therefore is missing:

只是我的解决方案，向您展示表的连接。事实上，除了CROSS JOIN的其他解决方案之外别无其他... Person4未激活，因此缺失：

declare @bankholidays TABLE(ID INT,TheDate DATE);
insert into @bankholidays values(1,{ts'2015-08-31 00:00:00'}),(2,{ts'2015-12-25 00:00:00'});

declare @staff TABLE(StaffID INT,StaffName VARCHAR(100could),Active BIT);
insert into @staff VALUES(1,'Person1',1),(2,'Person2',1),(3,'Person3',1),(4,'Person4',0),(5,'Person5',1);

declare @target TABLE(targetID INT,CalendarID INT,CalendarDate DATE,StaffID INT);

INSERT INTO @target
SELECT ROW_NUMBER() OVER(ORDER BY bh.TheDate,s.StaffID)
      ,bh.ID
      ,bh.TheDate
      ,s.StaffID
FROM @staff AS s,@bankholidays AS bh
WHERE s.Active=1;

SELECT * FROM @target

#1