Forum Discussion

ArarA911's avatar
ArarA911
Copper Contributor
Jul 03, 2023

repeat records in a script

dears,

good day!!

 

i have a script where my date column is 
convert (varchar(6),getdate(),112) as Mnth_code

and i want to repeat all records till the end of the year 

current month is 202307 i want to repeat same records till 202312

your kind support is highly appreciated

thank you 

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jul 03, 2023

    ArarA911 , use a recursive CTE.

    Important is MAXRECURSION, default is max 100 and so without it would fail

    ;WITH cte AS
    (SELECT convert (date, GETDATE(), 112) AS MyDate
	 UNION ALL
	 SELECT DATEADD(day, 1, cte.MyDate)
	 FROM cte
	 WHERE cte.MyDate < '20231231')
SELECT *
FROM cte
OPTION (MAXRECURSION 366);

     

Resources