December 4, 2015 at 12:01 pm
First of all, you need to reference the CTE through a SELECT statement. Something like this:
--Insert a reservation for a key during specific dates
WITH Dates AS (
SELECT
[Date] = CONVERT(date,'20151127')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '20151202'
)
INSERT RX.ReservationTB(LicenseKey, RentalOrderNum, ReservationDate)
SELECT '7WP6V-W2Y33-GKH2B-6YTD4-K2WPR', 'RO11', [Date]
FROM Dates
OPTION (MAXRECURSION 45)
Second, you need to change that recursive query as it might give you performance problems. Check this for more information: http://www.sqlservercentral.com/articles/T-SQL/74118/
The alternative could be using cascade CTEs
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
Dates([Date]) AS(
SELECT TOP (DATEDIFF(dd, '20151127', '20151202') + 1 )
CONVERT(date,DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20151127')) [Date]
FROM E4
)
INSERT RX.ReservationTB(LicenseKey, RentalOrderNum, ReservationDate)
SELECT '7WP6V-W2Y33-GKH2B-6YTD4-K2WPR', 'RO11', [Date]
FROM Dates
Last, your second query has nothing weird. Please read the article in my signature to give you a better idea on how to post so we can understand and help better and faster.
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply