如何用SQL将work_events表行每周重复复制51次并递增ID？

核心思路是生成一个包含51个递增数值的序列，基于原数据的字段值计算出每条新记录的id和date_time，再批量插入到表中。以下是主流数据库的具体实现方案：

MySQL（8.0及以上版本） #

利用递归CTE生成序列：

WITH RECURSIVE sequence AS (
    SELECT 1 AS offset_num
    UNION ALL
    SELECT offset_num + 1 FROM sequence WHERE offset_num < 51
)
INSERT INTO work_events (id, date_time, title)
SELECT 
    (SELECT MAX(id) FROM work_events) + offset_num,
    DATE_ADD((SELECT date_time FROM work_events WHERE id = 1), INTERVAL (offset_num * 7) DAY),
    (SELECT title FROM work_events WHERE id = 1)
FROM sequence;

PostgreSQL #

用generate_series直接生成序列，写法更简洁：

INSERT INTO work_events (id, date_time, title)
SELECT 
    (SELECT MAX(id) FROM work_events) + s,
    (SELECT date_time FROM work_events WHERE id = 1) + (s * INTERVAL '7 days'),
    (SELECT title FROM work_events WHERE id = 1)
FROM generate_series(1, 51) AS s;

SQL Server #

通过递归CTE实现，需添加递归次数限制参数：

WITH sequence AS (
    SELECT 1 AS offset_num
    UNION ALL
    SELECT offset_num + 1 FROM sequence WHERE offset_num < 51
)
INSERT INTO work_events (id, date_time, title)
SELECT 
    (SELECT MAX(id) FROM work_events) + offset_num,
    DATEADD(DAY, offset_num * 7, (SELECT date_time FROM work_events WHERE id = 1)),
    (SELECT title FROM work_events WHERE id = 1)
FROM sequence
OPTION (MAXRECURSION 0);

注意事项 #

• 如果id是数据库自增主键（比如MySQL的AUTO_INCREMENT、PostgreSQL的SERIAL、SQL Server的IDENTITY），可以省略id字段的手动计算，数据库会自动生成递增的id值。
• 执行插入前，建议先单独运行SELECT部分，确认生成的新记录数据符合预期后再执行INSERT。
• 确保原数据（id=1的记录）存在，否则子查询会返回NULL，导致插入失败。

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