SQL Server 2008 中的 while 循环遍历日期范围然后插入

Question

提问by Armin

I have a table with a few columns, one of which is a Timestamp column. But currently in this table there is not a record for each day. Meaning, there are records for January 1st, and January 2nd, but no records with January 3rd or January 4th in the Timestamp field. However, there are records continued for January 5th, and January 6th, and so on. Basically, weekends and other random days are missing.

我有一个包含几列的表格，其中一个是时间戳列。但是目前在这个表中没有每天的记录。意思是，时间戳字段中有 1 月 1 日和 1 月 2 日的记录，但没有 1 月 3 日或 1 月 4 日的记录。但是，1 月 5 日和 1 月 6 日的记录仍在继续，依此类推。基本上，周末和其他随机的日子都不见了。

I'm trying to write a script that will scan this table from StartDate to EndDate (whatever date range I choose), and iterate through this date-range, and if a record does not exist with any of the dates in this date-range, insert a new record with that particular date in the Timestamp field, but empty/NULL data for the rest of the fields.

我正在尝试编写一个脚本，该脚本将从 StartDate 到 EndDate（我选择的任何日期范围）扫描此表，并遍历此日期范围，如果此日期范围中的任何日期不存在记录，在时间戳字段中插入具有该特定日期的新记录，但其余字段的数据为空/NULL。

Here's the pseudo-code I've got so far, and I think this is the right approach:

这是我到目前为止的伪代码，我认为这是正确的方法：

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    SELECT * FROM myTable WHERE myTable.Timestamp = "@CurrentDate"
    IF @@ROWCOUNT < 1
        print @CurrentDate
        /*insert a new row query here*/

    SET @CurrentDate = convert(varchar(30), dateadd(day,1, @CurrentDate), 101); /*increment current date*/
END

Here's the SQLFiddle - http://sqlfiddle.com/#!6/06c73/1

这是 SQLFiddle - http://sqlfiddle.com/#!6/06c73/1

I'm writing my first script in SQL Server Management Studio 2008 and I have something that I think might be for an intermediate user. I am a PHP/MySQL developer, and am very familiar with those technologies, but I am brand new to SQL and VBScript. I understand programming concepts and logic, but this seems much different than what I'm used to.

我正在 SQL Server Management Studio 2008 中编写我的第一个脚本，我认为有些东西可能适合中级用户。我是一名 PHP/MySQL 开发人员，对这些技术非常熟悉，但我对 SQL 和 VBScript 是全新的。我了解编程概念和逻辑，但这似乎与我习惯的大不相同。

I greatly appreciate all help and insight in advance!

我非常感谢所有的帮助和洞察力！

Answer 1

回答by GarethD

SQL is a set based language and loops should be a last resort. So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops:

SQL 是一种基于集合的语言，循环应该是最后的手段。因此，基于集合的方法是首先生成您需要的所有日期并一次性插入它们，而不是一次循环插入一个。Aaron Bertrand 写了一个关于生成没有循环的集合或序列的很棒的系列：

Part 3 is specifically relevant as it deals with dates.

第 3 部分特别相关，因为它涉及日期。

Assuming you don't have a Calendar table you can use the stacked CTE method to generate a list of dates between your start and end dates.

假设您没有日历表，您可以使用堆叠 CTE 方法生成开始日期和结束日期之间的日期列表。

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3;

I have skipped some detail on how this works as it is covered in the linked article, in essence it starts with a hard coded table of 10 rows, then joins this table with itself to get 100 rows (10 x 10) then joins this table of 100 rows to itself to get 10,000 rows (I stopped at this point but if you require further rows you can add further joins).

我跳过了一些关于它是如何工作的细节，因为它在链接的文章中有所介绍，本质上它从一个 10 行的硬编码表开始，然后将此表与自身连接以获得 100 行 (10 x 10)，然后连接此表100 行到自己得到 10,000 行（我在这一点上停止，但如果你需要更多的行，你可以添加更多的连接）。

At each step the output is a single column called Nwith a value of 1 (to keep things simple). At the same time as defining how to generate 10,000 rows, I actually tell SQL Server to only generate the number needed by using TOPand the difference between your start and end date - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1). This avoids unnecessary work. I had to add 1 to the difference to ensure both dates were included.

在每一步，输出都是一个单列N，其值为 1（为简单起见）。在定义如何生成 10,000 行的同时，我实际上告诉 SQL Server 只生成使用所需的数量TOP以及您的开始和结束日期之间的差异 - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1)。这避免了不必要的工作。我必须在差值上加 1 以确保包括两个日期。

Using the ranking function ROW_NUMBER()I add an incremental number to each of the rows generated, then I add this incremental number to your start date to get the list of dates. Since ROW_NUMBER()begins at 1, I need to deduct 1 from this to ensure the start date is included.

使用排名函数，ROW_NUMBER()我向生成的每一行添加一个增量编号，然后将此增量编号添加到您的开始日期以获取日期列表。由于ROW_NUMBER()从 1 开始，我需要从中扣除 1 以确保包括开始日期。

Then it would just be a case of excluding dates that already exist using NOT EXISTS. I have enclosed the results of the above query in their own CTE called dates:

那么这只是排除已经存在的日期的一种情况NOT EXISTS。我已将上述查询的结果包含在他们自己的 CTE 中，称为dates：

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
)
INSERT INTO MyTable ([TimeStamp])
SELECT  Date
FROM    Dates AS d
WHERE NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE d.Date = t.[TimeStamp])

Example on SQL Fiddle

SQL Fiddle 示例

If you were to create a calendar table (as described in the linked articles) then it may not be necessary to insert these extra rows, you could just generate your result set on the fly, something like:

如果您要创建日历表（如链接文章中所述），则可能不需要插入这些额外的行，您可以即时生成结果集，例如：

SELECT  [Timestamp] = c.Date,
        t.[FruitType],
        t.[NumOffered],
        t.[NumTaken],
        t.[NumAbandoned],
        t.[NumSpoiled]
FROM    dbo.Calendar AS c
        LEFT JOIN dbo.MyTable AS t
            ON t.[Timestamp] = c.[Date]
WHERE   c.Date >= @StartDate
AND     c.Date < @EndDate;

ADDENDUM

附录

To answer your actual question your loop would be written as follows:

要回答您的实际问题，您的循环将编写如下：

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM myTable WHERE myTable.Timestamp = @CurrentDate)
    BEGIN
        INSERT INTO MyTable ([Timestamp])
        VALUES (@CurrentDate);
    END

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/
END

Example on SQL Fiddle

SQL Fiddle 示例

I do not advocate this approach, just because something is only being done once does not mean that I should not demonstrate the correct way of doing it.

我不提倡这种方法，仅仅因为某件事只做一次并不意味着我不应该展示正确的做法。

FURTHER EXPLANATION

进一步说明

Since the stacked CTE method may have over complicated the set based approach I will simplify it by using the undocumented system table master..spt_values. If you run:

由于堆叠 CTE 方法可能使基于集合的方法过于复杂，我将通过使用未记录的系统表来简化它master..spt_values。如果你运行：

SELECT Number
FROM master..spt_values
WHERE Type = 'P';

You will see that you get all the numbers from 0 -2047.

您将看到您获得了 0 -2047 之间的所有数字。

Now if you run:

现在，如果你运行：

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();


SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P';

You get all the dates from your start date to 2047 days in the future. If you add a further where clause you can limit this to dates before your end date:

您可以获得从开始日期到未来 2047 天的所有日期。如果您添加进一步的 where 子句，您可以将其限制为结束日期之前的日期：

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();


SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate;

Now you have all the dates you need in a single set based query you can eliminate the rows that already exist in your table using NOT EXISTS

现在，您在基于单个集合的查询中拥有所需的所有日期，您可以使用以下方法消除表中已存在的行 NOT EXISTS

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();


SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Finally you can insert these dates into your table using INSERT

最后，您可以使用将这些日期插入到您的表中 INSERT

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

INSERT YourTable ([Timestamp])
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Hopefully this goes some way to showing that the set based approach is not only much more efficient it is simpler too.

希望这在某种程度上表明基于集合的方法不仅效率更高，而且更简单。

SQL Server 2008 中的 while 循环遍历日期范围然后插入

提问by Armin

回答by GarethD

Example on SQL Fiddle

SQL Fiddle 示例

相关推荐

最近更新

标签

SQL Server 2008 中的 while 循环遍历日期范围然后插入

提问by Armin

回答by GarethD

Example on SQL Fiddle

SQL Fiddle 示例

相关推荐

将 SQL Server 日期转换为 mm-yyyy

为什么我的表没有显示在 SQL Server Management Studio 的智能感知中？

SQL 如何使用PROC SQL查找数值变量的长度

PL SQL 中声明时变量的默认值

相关推荐

最近更新

标签