如何将WITH子句生成的补全数据保存为currency_converter_new表？

现有currency_converter汇率表，缺失周末日期数据，已通过WITH子句查询实现用周五汇率填充周六、周日数据，得到期望结果集，但无法将结果插入到currency_converter_new表中。执行插入前会先截断目标表，求正确实现方法。

原表数据（currency_converter） #

期望目标表数据（currency_converter_new） #

生成结果的正确查询语句 #

WITH currency AS (
    SELECT 
        *, 
        LEAD(time_period) OVER (PARTITION BY currency ORDER BY time_period) AS next_time_period
    FROM currency_converter
)
SELECT 
    c.day AS time_period, 
    t.obs_value, 
    t.currency
FROM dim_calendar c
JOIN currency t
    ON c.day BETWEEN t.time_period AND ISNULL(DATEADD(day, -1, t.next_time_period), t.time_period)

（注：原查询中字段名valuta应为currency，已修正）

尝试失败的INSERT语句 #

with currency AS
(SELECT *, LEAD(time_period) OVER (PARTITION BY valuta ORDER BY 
time_period) as next_time_period
FROM currency_converter
);
INSERT INTO (currency_converter(time_period, obs_value, valuta)
SELECT * FROM ( 
SELECT c.day as time_period, t.obs_value, t.valuta
FROM dim_calendar c
JOIN currency t
ON c.day BETWEEN t.time_period and ISNULL(DATEADD(day, -1, 
t.next_time_period), t.time_period)
)

错误分析 #

1. CTE（WITH子句）定义后不能加分号，且CTE需与后续INSERT语句在同一批执行
2. INSERT INTO语法错误：目标表名不应加括号，字段列表格式不符合规范
3. 子查询缺少别名，且字段名valuta需与原表currency统一

正确实现步骤 #

1. 先截断目标表（执行前确认数据已备份）
2. 将CTE与INSERT INTO结合，直接从CTE查询结果插入目标表

正确SQL语句 #

-- 1. 截断目标表（执行前确认数据已备份）
TRUNCATE TABLE currency_converter_new;

-- 2. 插入填充后的数据
WITH currency AS (
    SELECT 
        *, 
        LEAD(time_period) OVER (PARTITION BY currency ORDER BY time_period) AS next_time_period
    FROM currency_converter
)
INSERT INTO currency_converter_new (time_period, obs_value, currency)
SELECT 
    c.day AS time_period, 
    t.obs_value, 
    t.currency
FROM dim_calendar c
JOIN currency t
    ON c.day BETWEEN t.time_period AND ISNULL(DATEADD(day, -1, t.next_time_period), t.time_period);

兼容老版本数据库的替代方案 #

若使用的数据库不支持INSERT中直接使用CTE（如部分老版本MySQL），可改用子查询方式：

TRUNCATE TABLE currency_converter_new;

INSERT INTO currency_converter_new (time_period, obs_value, currency)
SELECT 
    c.day AS time_period, 
    t.obs_value, 
    t.currency
FROM dim_calendar c
JOIN (
    SELECT 
        *, 
        LEAD(time_period) OVER (PARTITION BY currency ORDER BY time_period) AS next_time_period
    FROM currency_converter
) t
    ON c.day BETWEEN t.time_period AND ISNULL(DATEADD(day, -1, t.next_time_period), t.time_period);

内容的提问来源于stack exchange，提问作者daffy_daf