Loop is a repeated call of a part of a code with different parameters. Usually the code is bound to certain DML operation such as INSERT or UPDATE. We call out this cycle for predetermined number of repetitions and it can be built in SQL in different ways. We will take a look at all ways using a calendar as an example.
How to Generate Calendar in SQL Using Loop?
Let’s say we want to create a table with calendar in our database with calendar starting on 1/1/1980 and ending on 31/12/2050. We can obviously use more elegant ways to establish calendar in the database but we will take this as an exercise.
Create empty table with calendar as a preparation using the script below:
CREATE TABLE #Calendar(
[Date] [Date] PRIMARY KEY,
[DayOfYear] AS (DATEPART(DAYOFYEAR,[Date])),
[DayOfWeek] AS (DATEPART(WEEKDAY,[Date])),
[Month] AS (DATENAME(MONTH,DATEADD(MONTH,DATEPART(MONTH,[Date]),(0))-(1))),
[Quarter] AS (CONCAT(DATEPART(QUARTER,[Date]),'Q')),
[Year] AS (DATEPART(YEAR,[Date]))
);
You can see only column we have to insert is date. The remain is all calculated columns based on Date column. This way we do not need to insert whole list of fields but only [Date].
1. GOTO method of looping in SQL Server
This method is not recommended much since it is much slower than the other methods. It consists of label definition (e.g. “SQL_LOOP_START:”) to which we return by command GOTO in particular iterations.
DECLARE @date AS SMALLDATETIME = '1980-01-01';
DECLARE @date_end AS SMALLDATETIME = '2050-12-31';
SQL_LOOP_START:
INSERT INTO #Calendar ([date])
VALUES(@date)
SET @date = @date+1
IF @date < @date_end
GOTO SQL_LOOP_START;
SELECT * FROM #Calendar;
2. WHILE/BEGIN/END Loop
Command WHILE is used to execute repeated call of code following after WHILE (usually separated BEGIN/END) until the requirement following after WHILE is in effect.
/*delete the dates in our table and enter the dates in a different way*/
TRUNCATE TABLE #Calendar;
DECLARE @date AS SMALLDATETIME = '1980-01-01'
DECLARE @date_end AS SMALLDATETIME = '2050-12-31'
WHILE @date< @date_end
BEGIN
INSERT INTO #Calendar ([date])
VALUES(@date)
SET @date = @date+1
END;
3. Recursive CTE (Common table expression)
Recursive CTE is by far the fastest way to achieve the needed result:
/*delete the dates in our table and enter the dates in a different way*/
TRUNCATE TABLE #Calendar;
DECLARE @date AS SMALLDATETIME = '1980-01-01';
DECLARE @date_end AS SMALLDATETIME = '2050-12-31'
;WITH [CTE_Date] ([Date]) AS (
SELECT @date AS DATUM
UNION ALL
SELECT [DATE]+1 AS DATE
FROM [CTE_Date]
WHERE [DATE] + 1<@date_end
)
INSERT INTO #Calendar
SELECT * FROM [CTE_Date] OPTION (MAXRECURSION 32767);
More about recursive queries on Microsoft website