我通过网络研究和自己的SQL知识（不是最大的知识）拼凑了以下代码。
该表Table_One包含员工及其工作日的数据，我要做的是INSERT在每个工作人员两个指定日期之间缺少日期（非工作日）的行。
Table_One
INSERT
据我所知，我不知道如何检查该行是否丢失，如果存在，请插入row带有日期和相应工作人员数据的新行。
row
SET NOCOUNT ON; IF object_id('dbo.Tally') is not null drop table dbo.tally GO SELECT TOP 10000 IDENTITY(int,1,1) as ID INTO dbo.Tally FROM master.dbo.SysColumns ALTER table dbo.Tally add constraint PK_ID primary key clustered(ID) GO select * from dbo.Tally --Generate Date Range DECLARE @StartDate datetime DECLARE @EndDate datetime SET @StartDate = '2016/6/1' SET @EndDate= '2016/7/1' SELECT dateadd(DD,ID-1,@StartDate) as [DATE] FROM dbo.Tally WHERE dateadd(DD,ID-1,@StartDate)<=@EndDate
桌子看起来像这样
Staff_ID | Date | Year | Mon | Day | First_Name | Last_Name | Section | Time_Worked 1001 | 2016/6/1 | 2016 | 6 | 1 | Bill | Price | Level 1 | 2016/6/1 8:30:00.000 1001 | 2016/6/5 | 2016 | 6 | 1 | Bill | Price | Level 1 | 2016/6/5 8:30:00.000 1001 | 2016/6/9 | 2016 | 6 | 1 | Bill | Price | Level 1 | 2016/6/9 8:30:00.000 1001 | 2016/6/12 | 2016 | 6 | 1 | Bill | Price | Level 1 | 2016/6/12 8:30:00.000 1002 | 2016/6/1 | 2016 | 6 | 1 | Mary | Somers | Level 1 | 2016/6/1 8:30:00.000 1002 | 2016/6/5 | 2016 | 6 | 1 | Mary | Somers | Level 1 | 2016/6/5 8:30:00.000 1002 | 2016/6/8 | 2016 | 6 | 1 | Mary | Somers | Level 1 | 2016/6/8 8:30:00.000 1003 | 2016/6/3 | 2016 | 6 | 1 | Mark | Jones | Level 1 | 2016/6/3 8:30:00.000 1003 | 2016/6/5 | 2016 | 6 | 1 | Mark | Jones | Level 1 | 2016/6/5 8:30:00.000
该工作人员的两个日期之间的第一行数据将能够用于填充日期列以外的其他列。而且每个工作人员的第一行数据不一定都在同一日期。
例如。该工作人员的第一天是SET @StartDate = '2016/6/1'查询中的第二天，
SET @StartDate = '2016/6/1'
Staff_ID | Date | First_Name | Last_Name | Section | Time_Worked 1003 | 2016/6/3 | Mark | Jones | Level 1 | 2016/6/3 8:30:00.000
但是，其他列将能够用于填充新行数据。
这是上表中一名工作人员的结果，在这种情况下，工作人员编号1001为Bill。
1001
Bill
Staff_ID | Date | Year | Mon| Day| First_Name | Last_Name | Section | Time_Worked 1001 | 2016/6/1 | 2016 | 6 | 1 | Bill | Price | Level 1 | 2016/6/1 8:30:00.000 1001 | 2016/6/2 | 2016 | 6 | 2 | Bill | Price | Level 1 | NULL 1001 | 2016/6/3 | 2016 | 6 | 3 | Bill | Price | Level 1 | NULL 1001 | 2016/6/4 | 2016 | 6 | 4 | Bill | Price | Level 1 | NULL 1001 | 2016/6/5 | 2016 | 6 | 5 | Bill | Price | Level 1 | 2016/6/5 8:30:00.000 1001 | 2016/6/6 | 2016 | 6 | 6 | Bill | Price | Level 1 | NULL 1001 | 2016/6/7 | 2016 | 6 | 7 | Bill | Price | Level 1 | NULL 1001 | 2016/6/8 | 2016 | 6 | 8 | Bill | Price | Level 1 | NULL 1001 | 2016/6/9 | 2016 | 6 | 9 | Bill | Price | Level 1 | 2016/6/9 8:30:00.000 1001 | 2016/6/10 | 2016 | 6 | 10 | Bill | Price | Level 1 | NULL 1001 | 2016/6/11 | 2016 | 6 | 11 | Bill | Price | Level 1 | NULL 1001 | 2016/6/12 | 2016 | 6 | 12 | Bill | Price | Level 1 | 2016/6/12 8:30:00.000 1001 | 2016/6/13 | 2016 | 6 | 13 | Bill | Price | Level 1 | NULL 1001 | 2016/6/14 | 2016 | 6 | 14 | Bill | Price | Level 1 | NULL 1001 | 2016/6/15 | 2016 | 6 | 15 | Bill | Price | Level 1 | NULL 1001 | 2016/6/16 | 2016 | 6 | 16 | Bill | Price | Level 1 | NULL 1001 | 2016/6/17 | 2016 | 6 | 17 | Bill | Price | Level 1 | NULL 1001 | 2016/6/18 | 2016 | 6 | 18 | Bill | Price | Level 1 | NULL 1001 | 2016/6/19 | 2016 | 6 | 19 | Bill | Price | Level 1 | NULL 1001 | 2016/6/20 | 2016 | 6 | 20 | Bill | Price | Level 1 | NULL 1001 | 2016/6/21 | 2016 | 6 | 21 | Bill | Price | Level 1 | NULL 1001 | 2016/6/22 | 2016 | 6 | 22 | Bill | Price | Level 1 | NULL 1001 | 2016/6/23 | 2016 | 6 | 23 | Bill | Price | Level 1 | NULL 1001 | 2016/6/24 | 2016 | 6 | 24 | Bill | Price | Level 1 | NULL 1001 | 2016/6/25 | 2016 | 6 | 25 | Bill | Price | Level 1 | NULL 1001 | 2016/6/26 | 2016 | 6 | 26 | Bill | Price | Level 1 | NULL 1001 | 2016/6/27 | 2016 | 6 | 27 | Bill | Price | Level 1 | NULL 1001 | 2016/6/28 | 2016 | 6 | 28 | Bill | Price | Level 1 | NULL 1001 | 2016/6/29 | 2016 | 6 | 29 | Bill | Price | Level 1 | NULL 1001 | 2016/6/30 | 2016 | 6 | 30 | Bill | Price | Level 1 | NULL
我有一个While Loop正在为我工作并现在更新丢失的记录，但是性能太差了。
While Loop
谢谢
另一个选项：（再次更新） 创建并填充示例表（ 请 在您的下一个问题中将这一步保存给我们）
DECLARE @T as TABLE ( Staff_ID int, [Date] date, [Year] int, Mon int, [Day] int, First_Name varchar(10), Last_Name varchar(10), Section varchar(10), Time_Worked datetime ) INSERT INTO @T VALUES (1001, '2016-06-01', 2016, 6, 1, 'Bill', 'Price', 'Level 1', '2016-06-01 8:30:00.000'), (1001, '2016-06-05', 2016, 6, 5, 'Bill', 'Price', 'Level 1', '2016-06-05 8:30:00.000'), (1001, '2016-06-09', 2016, 6, 9, 'Bill', 'Price', 'Level 1', '2016-06-09 8:30:00.000'), (1001, '2016-07-05', 2016, 7, 5, 'Bill', 'Price', 'Level 2', '2016-06-12 8:30:00.000'), -- Different month (1002, '2016-06-01', 2016, 6, 1, 'Mary', 'Somers', 'Level 1', '2016-06-01 8:30:00.000'), (1002, '2016-06-05', 2016, 6, 5, 'Mary', 'Somers', 'Level 1', '2016-06-05 8:30:00.000'), (1002, '2016-06-08', 2016, 6, 8, 'Mary', 'Somers', 'Level 1', '2016-06-08 8:30:00.000'), (1003, '2016-06-03', 2016, 6, 3, 'Mark', 'Jones', 'Level 1', '2016-06-03 8:30:00.000'), (1003, '2016-06-04', 2016, 6, 4, 'Mark', 'Jones', 'Level 1', '2016-06-05 8:30:00.000')
声明并填充@StartDate和@EndDate：
@StartDate
@EndDate
DECLARE @StartDate datetime = '2016-06-01', @EndDate datetime = '2016-08-01'
该INSERT...SELECT语句：（我选择使用一个CTE，所以我就不用写了dateadd(DD,ID-1,@StartDate)这么多次）
INSERT...SELECT
dateadd(DD,ID-1,@StartDate)
;WITH Calendar AS ( SELECT dateadd(DD,ID-1,@StartDate) as [Date] FROM dbo.Tally WHERE dateadd(DD,ID-1,@StartDate) < @EndDate ) INSERT INTO @T (Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section) SELECT DISTINCT Staff_ID, C.[Date], Year(C.[Date]), MONTH(C.[Date]), DAY(C.[Date]), First_Name, Last_Name, Section FROM @T T CROSS APPLY ( SELECT Cal.[Date] FROM Calendar Cal WHERE MONTH(Cal.[Date]) = MONTH(T.[Date]) AND YEAR(Cal.[Date]) = YEAR(T.[Date]) AND NOT EXISTS ( SELECT 1 FROM @T T2 WHERE T.Staff_ID = T2.Staff_ID AND T2.[Date] = Cal.[Date] ) ) C
验证插入物：
SELECT Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section, Time_Worked FROM @T ORDER BY Staff_ID, [Date]
结果：
Staff_ID Date Year Mon Day First_Name Last_Name Section Time_Worked ----------- ---------- ----------- ----------- ----------- ---------- ---------- ---------- ----------------------- 1001 2016-06-01 2016 6 1 Bill Price Level 1 2016-06-01 08:30:00.000 1001 2016-06-02 2016 6 2 Bill Price Level 1 NULL 1001 2016-06-03 2016 6 3 Bill Price Level 1 NULL 1001 2016-06-04 2016 6 4 Bill Price Level 1 NULL 1001 2016-06-05 2016 6 5 Bill Price Level 1 2016-06-05 08:30:00.000 1001 2016-06-06 2016 6 6 Bill Price Level 1 NULL 1001 2016-06-07 2016 6 7 Bill Price Level 1 NULL 1001 2016-06-08 2016 6 8 Bill Price Level 1 NULL 1001 2016-06-09 2016 6 9 Bill Price Level 1 2016-06-09 08:30:00.000 1001 2016-06-10 2016 6 10 Bill Price Level 1 NULL 1001 2016-06-11 2016 6 11 Bill Price Level 1 NULL 1001 2016-06-12 2016 6 12 Bill Price Level 1 NULL 1001 2016-06-13 2016 6 13 Bill Price Level 1 NULL 1001 2016-06-14 2016 6 14 Bill Price Level 1 NULL 1001 2016-06-15 2016 6 15 Bill Price Level 1 NULL 1001 2016-06-16 2016 6 16 Bill Price Level 1 NULL 1001 2016-06-17 2016 6 17 Bill Price Level 1 NULL 1001 2016-06-18 2016 6 18 Bill Price Level 1 NULL 1001 2016-06-19 2016 6 19 Bill Price Level 1 NULL 1001 2016-06-20 2016 6 20 Bill Price Level 1 NULL 1001 2016-06-21 2016 6 21 Bill Price Level 1 NULL 1001 2016-06-22 2016 6 22 Bill Price Level 1 NULL 1001 2016-06-23 2016 6 23 Bill Price Level 1 NULL 1001 2016-06-24 2016 6 24 Bill Price Level 1 NULL 1001 2016-06-25 2016 6 25 Bill Price Level 1 NULL 1001 2016-06-26 2016 6 26 Bill Price Level 1 NULL 1001 2016-06-27 2016 6 27 Bill Price Level 1 NULL 1001 2016-06-28 2016 6 28 Bill Price Level 1 NULL 1001 2016-06-29 2016 6 29 Bill Price Level 1 NULL 1001 2016-06-30 2016 6 30 Bill Price Level 1 NULL 1001 2016-07-01 2016 7 1 Bill Price Level 2 NULL 1001 2016-07-02 2016 7 2 Bill Price Level 2 NULL 1001 2016-07-03 2016 7 3 Bill Price Level 2 NULL 1001 2016-07-04 2016 7 4 Bill Price Level 2 NULL 1001 2016-07-05 2016 7 5 Bill Price Level 2 2016-06-12 08:30:00.000 1001 2016-07-06 2016 7 6 Bill Price Level 2 NULL 1001 2016-07-07 2016 7 7 Bill Price Level 2 NULL 1001 2016-07-08 2016 7 8 Bill Price Level 2 NULL 1001 2016-07-09 2016 7 9 Bill Price Level 2 NULL 1001 2016-07-10 2016 7 10 Bill Price Level 2 NULL 1001 2016-07-11 2016 7 11 Bill Price Level 2 NULL 1001 2016-07-12 2016 7 12 Bill Price Level 2 NULL 1001 2016-07-13 2016 7 13 Bill Price Level 2 NULL 1001 2016-07-14 2016 7 14 Bill Price Level 2 NULL 1001 2016-07-15 2016 7 15 Bill Price Level 2 NULL 1001 2016-07-16 2016 7 16 Bill Price Level 2 NULL 1001 2016-07-17 2016 7 17 Bill Price Level 2 NULL 1001 2016-07-18 2016 7 18 Bill Price Level 2 NULL 1001 2016-07-19 2016 7 19 Bill Price Level 2 NULL 1001 2016-07-20 2016 7 20 Bill Price Level 2 NULL 1001 2016-07-21 2016 7 21 Bill Price Level 2 NULL 1001 2016-07-22 2016 7 22 Bill Price Level 2 NULL 1001 2016-07-23 2016 7 23 Bill Price Level 2 NULL 1001 2016-07-24 2016 7 24 Bill Price Level 2 NULL 1001 2016-07-25 2016 7 25 Bill Price Level 2 NULL 1001 2016-07-26 2016 7 26 Bill Price Level 2 NULL 1001 2016-07-27 2016 7 27 Bill Price Level 2 NULL 1001 2016-07-28 2016 7 28 Bill Price Level 2 NULL 1001 2016-07-29 2016 7 29 Bill Price Level 2 NULL 1001 2016-07-30 2016 7 30 Bill Price Level 2 NULL 1001 2016-07-31 2016 7 31 Bill Price Level 2 NULL 1002 2016-06-01 2016 6 1 Mary Somers Level 1 2016-06-01 08:30:00.000 1002 2016-06-02 2016 6 2 Mary Somers Level 1 NULL 1002 2016-06-03 2016 6 3 Mary Somers Level 1 NULL 1002 2016-06-04 2016 6 4 Mary Somers Level 1 NULL 1002 2016-06-05 2016 6 5 Mary Somers Level 1 2016-06-05 08:30:00.000 1002 2016-06-06 2016 6 6 Mary Somers Level 1 NULL 1002 2016-06-07 2016 6 7 Mary Somers Level 1 NULL 1002 2016-06-08 2016 6 8 Mary Somers Level 1 2016-06-08 08:30:00.000 1002 2016-06-09 2016 6 9 Mary Somers Level 1 NULL 1002 2016-06-10 2016 6 10 Mary Somers Level 1 NULL 1002 2016-06-11 2016 6 11 Mary Somers Level 1 NULL 1002 2016-06-12 2016 6 12 Mary Somers Level 1 NULL 1002 2016-06-13 2016 6 13 Mary Somers Level 1 NULL 1002 2016-06-14 2016 6 14 Mary Somers Level 1 NULL 1002 2016-06-15 2016 6 15 Mary Somers Level 1 NULL 1002 2016-06-16 2016 6 16 Mary Somers Level 1 NULL 1002 2016-06-17 2016 6 17 Mary Somers Level 1 NULL 1002 2016-06-18 2016 6 18 Mary Somers Level 1 NULL 1002 2016-06-19 2016 6 19 Mary Somers Level 1 NULL 1002 2016-06-20 2016 6 20 Mary Somers Level 1 NULL 1002 2016-06-21 2016 6 21 Mary Somers Level 1 NULL 1002 2016-06-22 2016 6 22 Mary Somers Level 1 NULL 1002 2016-06-23 2016 6 23 Mary Somers Level 1 NULL 1002 2016-06-24 2016 6 24 Mary Somers Level 1 NULL 1002 2016-06-25 2016 6 25 Mary Somers Level 1 NULL 1002 2016-06-26 2016 6 26 Mary Somers Level 1 NULL 1002 2016-06-27 2016 6 27 Mary Somers Level 1 NULL 1002 2016-06-28 2016 6 28 Mary Somers Level 1 NULL 1002 2016-06-29 2016 6 29 Mary Somers Level 1 NULL 1002 2016-06-30 2016 6 30 Mary Somers Level 1 NULL 1003 2016-06-01 2016 6 1 Mark Jones Level 1 NULL 1003 2016-06-02 2016 6 2 Mark Jones Level 1 NULL 1003 2016-06-03 2016 6 3 Mark Jones Level 1 2016-06-03 08:30:00.000 1003 2016-06-04 2016 6 4 Mark Jones Level 1 2016-06-05 08:30:00.000 1003 2016-06-05 2016 6 5 Mark Jones Level 1 NULL 1003 2016-06-06 2016 6 6 Mark Jones Level 1 NULL 1003 2016-06-07 2016 6 7 Mark Jones Level 1 NULL 1003 2016-06-08 2016 6 8 Mark Jones Level 1 NULL 1003 2016-06-09 2016 6 9 Mark Jones Level 1 NULL 1003 2016-06-10 2016 6 10 Mark Jones Level 1 NULL 1003 2016-06-11 2016 6 11 Mark Jones Level 1 NULL 1003 2016-06-12 2016 6 12 Mark Jones Level 1 NULL 1003 2016-06-13 2016 6 13 Mark Jones Level 1 NULL 1003 2016-06-14 2016 6 14 Mark Jones Level 1 NULL 1003 2016-06-15 2016 6 15 Mark Jones Level 1 NULL 1003 2016-06-16 2016 6 16 Mark Jones Level 1 NULL 1003 2016-06-17 2016 6 17 Mark Jones Level 1 NULL 1003 2016-06-18 2016 6 18 Mark Jones Level 1 NULL 1003 2016-06-19 2016 6 19 Mark Jones Level 1 NULL 1003 2016-06-20 2016 6 20 Mark Jones Level 1 NULL 1003 2016-06-21 2016 6 21 Mark Jones Level 1 NULL 1003 2016-06-22 2016 6 22 Mark Jones Level 1 NULL 1003 2016-06-23 2016 6 23 Mark Jones Level 1 NULL 1003 2016-06-24 2016 6 24 Mark Jones Level 1 NULL 1003 2016-06-25 2016 6 25 Mark Jones Level 1 NULL 1003 2016-06-26 2016 6 26 Mark Jones Level 1 NULL 1003 2016-06-27 2016 6 27 Mark Jones Level 1 NULL 1003 2016-06-28 2016 6 28 Mark Jones Level 1 NULL 1003 2016-06-29 2016 6 29 Mark Jones Level 1 NULL 1003 2016-06-30 2016 6 30 Mark Jones Level 1 NULL
