SQLTeam.com | Weblogs | Forums

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

I have a query that I am converting a date and inserting the data into a table and I keep getDI_ting the following error?
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Here is my query:
with cte as (
select datefromparts(year(DUC_Start), month(DUC_Start), 1) dt, API, \Well, Well_Num, Operator, County, State, DI_Basin, DI_Play, Producing_Reservoir, MD, Production_Type, Well_Status, Spud_Date, Completion_Date, DUC_Start, DUC_End, Drill_Type, GPI, Lateral_Length, First_Prod_Date, Section, Township, Range, Abstract, Block, Survey, SH_LATITUDE,
SH_LONGITUDE, BH_LATITUDE, BH_LONGITUDE
from Historical_Duc_Data t
union all
select dateadd(month, 1, dt) dt, API, Well, Well_Num, Operator, County, State, DI_Basin, DI_Play, Producing_Reservoir, MD, Production_Type, Well_Status, Spud_Date, Completion_Date, DUC_Start, DUC_End, Drill_Type, GPI, Lateral_Length, First_Prod_Date, Section, Township, Range, Abstract, Block, Survey, SH_LATITUDE,
SH_LONGITUDE, BH_LATITUDE, BH_LONGITUDE
from cte c
where c.dt <= DUC_End - 30
)

INSERT INTO [dbo].[Historical_Ducs_By_Basin] (
[dt],
[API],
[Well],
[Well_Num],
[Operator],
[County],
[State],
[DI_Basin],
[DI_Play],
[Producing_Reservoir],
[MD],
[Production_Type],
[Well_Status],
[Spud_Date],
[Completion_Date],
[DUC_Start],
[DUC_End],
[Drill_Type],
[GPI],
[Lateral_Length],
[First_Prod_Date],
[Section],
[Township],
[Range],
[Abstract],
[Block],
[Survey],
[SH_LATITUDE],
[SH_LONGITUDE],
[BH_LATITUDE],
[BH_LONGITUDE]
)

select * from cte

what are you trying to do? it looks you're getting all data from Historical_Duc_Data and generating the dt to be the 1st of the month. What's the second part doing? I don't know what DUC_End is, but do you want all the data from that table then records where the 1st of the month is more than 30 days older than the DUC_End? just trying to understand your requirements. Seems like a long way to get what you really need