Lors de la boucle dans SQL Server 2008 itérer sur une date, puis INSÉREZ

SQL est un jeu basé sur la langue et boucles devrait être un dernier recours. De sorte que la mise en fonction de l'approche serait d'abord de générer toutes les dates qui vous conviennent et de les insérer en une seule fois, plutôt que de le bouclage et l'insertion d'un à la fois. Aaron Bertrand a écrit une grande série sur la génération d'un ensemble ou d'une séquence sans boucles:

• Générer un ensemble ou d'une séquence sans boucles – partie 1
• Générer un ensemble ou d'une séquence sans boucles – partie 2
• Générer un ensemble ou d'une séquence sans boucles – partie 3

La partie 3 est particulièrement pertinent car il traite avec les dates.

En supposant que vous n'avez pas de Calendrier de table, vous pouvez utiliser le bac de CTE méthode pour générer une liste de dates entre le début et dates de fin.

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;

J'ai sauté quelques détails sur la façon dont cela fonctionne, qu'il est protégé par l'article lié, en substance, il commence avec une codés en dur tableau de 10 lignes, puis se joint à cette table avec elle-même pour obtenir 100 lignes (10 x 10), puis se joint à cette table de 100 lignes à elle-même pour obtenir 10 000 lignes (je me suis arrêté à ce point, mais si vous avez besoin de plus de lignes, vous pouvez ajouter d'autres jointures).

À chaque étape de la production est une seule colonne appelée N avec une valeur de 1 (pour garder les choses simples). Dans le même temps que la définition de la façon de générer 10 000 lignes, en fait, je dis SQL Server pour générer seulement le nombre requis par l'aide de TOP et la différence entre le début et date de fin - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1). Cela évite de travail inutile. J'ai dû ajouter 1 à la différence d'assurer à la fois les dates ont été inclus.

À l'aide de la fonction de classement ROW_NUMBER() - je ajouter un nombre supplémentaire pour chacune des lignes générées, puis-je ajouter ce numéro incrémentiel à la date de votre départ pour obtenir la liste des dates. Depuis ROW_NUMBER() commence à 1, j'ai besoin de déduire 1 à partir de cela pour s'assurer que la date de départ est inclus.

Alors il serait juste être un cas d'exclusion de dates qui existent déjà à l'aide de NOT EXISTS. J'ai joint les résultats de la requête ci-dessus dans leur propre CTE appelé 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])

Exemple sur SQL Violon

---

Si vous deviez créer un calendrier de table (comme décrit dans les articles liés), alors il peut ne pas être nécessaire d'insérer ces lignes supplémentaires, vous pouvez simplement créer votre jeu de résultats à la volée, quelque chose comme:

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;

---

ADDITIF

Pour répondre à votre question réelle de votre boucle serait écrit comme suit:

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

Exemple sur SQL Violon

Je ne préconise pas cette approche, tout simplement parce que quelque chose est fait une fois ne signifie pas que je ne devrait pas démontrer la bonne façon de faire.

---

DE PLUS AMPLES EXPLICATIONS

Depuis le empilés CTE méthode peut avoir plus compliqué de le définir en fonction de l'approche que je vais simplifier en utilisant les sans-papiers du système de tableau master..spt_values. Si vous exécutez:

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

Vous verrez que vous obtenez tous les nombres de 0 -2047.

Maintenant, si vous exécutez:

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


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

Vous obtenez toutes les dates à partir de la date de votre départ à 2047 jours dans le futur. Si vous ajouter une clause where permet de le limiter à des dates avant votre date de fin:

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;

Maintenant vous avez toutes les dates dont vous avez besoin dans un seul jeu basé sur une requête, vous pouvez éliminer les lignes qui existent déjà dans votre table à l'aide de 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));

Enfin, vous pouvez insérer ces dates dans votre table à l'aide de 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));

J'espère que cela va d'une certaine manière de montrer que le jeu de base est non seulement beaucoup plus efficace, il est plus simple aussi.

OriginalL'auteur GarethD