While 循环创建重复记录

I have a very simple query that takes two dates (start and end date, ex: 01/01/2018-01/31/2018) and then take out the week number of start and end date (ex: 1 and 5). I use while loop in order to enter all week numbers in one table (ex: 1,2,3,4,5). My while loop is creating duplicate records, like multiple records for all values. I am not sure what the issue is. Can someone suggest what I am might be doing wrong?

代码:

DECLARE @StartDate datetime = '01/01/2018'
    ,@EndDate datetime = '01/31/2018'
DECLARE @StartDateWeek INT  = DATEPART(WK,@StartDate)
    ,@EndDateWeek INT   = DATEPART(WK,@EndDate)
SELECT @StartDateWeek,@EndDateWeek

DECLARE @DateRangeWeekList TABLE(
Date DATE
,WeekNumber INT)

INSERT INTO @DateRangeWeekList
SELECT  Date = @StartDate
        ,WeekNumber = @StartDateWeek
select * from @DateRangeWeekList


WHILE (SELECT MAX(WeekNumber) FROM @DateRangeWeekList) < (SELECT     @EndDateWeek)
BEGIN
INSERT INTO @DateRangeWeekList
SELECT  Date = null
        ,WeekNumber = WeekNumber +1
FROM @DateRangeWeekList
END

select * from @DateRangeWeekList

结果:

Date        WeekNumber
2018-01-01  1
NULL        2
NULL        2
NULL        3
NULL        2
NULL        3
NULL        3
NULL        4
NULL        2 and more (total 18 records)

预期结果

Date       WeekNumber
2018-01-01 1
NULL       2
NULL       3
NULL       4
NULL       5

推荐答案

递归 CTE 在这里比 WHILE 循环要好得多:

DECLARE @StartDate datetime = '01/01/2018',
        @EndDate datetime = '01/31/2018';

WITH WeekNumbers AS(
    SELECT CONVERT(date,@StartDate) AS [Date],
           1 AS WeekNumber
    UNION ALL
    SELECT NULL,
           WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber <= DATEDIFF(WEEK, @StartDate, @EndDate))
SELECT *
FROM WeekNumbers;