插入 CTE 结果中的值

Question

我有'学年开始结束' 表

CREATE TABLE SchoolYearStartEnd (
id INT PRIMARY KEY UNIQUE,
StartDate DATE,
EndDate DATE
);

和第二个"学年教学日"表

CREATE TABLE SchoolYearsTeachingDays (
aDate DATE PRIMARY KEY UNIQUE
);

我想填写来自 CTE 的日期，如下所示：

WITH RECURSIVE dates(x) AS (
SELECT (SELECT StartDate FROM SchoolYearStartEnd)
UNION ALL
SELECT DATE(x, '+1 DAYS') FROM dates WHERE x < (SELECT EndDate FROM SchoolYearStartEnd)
)
SELECT * FROM dates WHERE CAST(STRFTIME('%w',x) AS INTEGER) > 0
;

我在这里尝试了这段代码：

INSERT INTO SchoolYearsTeachingDays (aDate) VALUES (
WITH RECURSIVE dates(x) AS (
SELECT (SELECT StartDate FROM SchoolYearStartEnd)
UNION ALL
SELECT DATE(x, '+1 DAYS') FROM dates WHERE x < (SELECT EndDate FROM SchoolYearStartEnd)
)
SELECT * FROM dates WHERE CAST(STRFTIME('%w',x) AS INTEGER) > 0 -- To exclude Sundays.
;
);

但没有成功。我收到这些错误：

Error: near "RECURSIVE": syntax error
Error: near ")": syntax error

那么我在这里错过了什么？

最佳，朋友

Answer 1

从 SELECT 查询插入时，不得使用 VALUES：

INSERT INTO SchoolYearsTeachingDays (aDate)
WITH RECURSIVE dates(x) AS (...)
SELECT * FROM dates ...;