如何批量插入WorkingSchedule数据仅修改workingdate字段

方法1：扩展VALUES子句（最直接） #

直接在VALUES后添加多组值，仅修改workingdate字段，其余字段保持不变，适合日期数量不多的场景：

insert into WorkingSchedule(
    employeeid,
    workingdate,
    DepartmentId,
    WorkingShiftIdForecast,
    WorkingShiftIdSEPE,
    WorkingShiftIdActual,
    notes,
    status,
    ActualStartTime1,
    ActualEndTime1,
    ActualStartTime2,
    ActualEndTime2,
    TimeAttendanceId
)
values 
(754, '2022-09-10', 6, 0,0,0,0,0,0,0,0,0, 10149),
(754, '2022-09-11', 6, 0,0,0,0,0,0,0,0,0, 10149),
(754, '2022-11-20', 6, 0,0,0,0,0,0,0,0,0, 10149); -- 可继续追加更多日期

方法2：使用SELECT + UNION ALL生成日期集合 #

若需插入的日期较多，用SELECT配合UNION ALL批量生成数据，减少固定字段的重复书写：

insert into WorkingSchedule(
    employeeid,
    workingdate,
    DepartmentId,
    WorkingShiftIdForecast,
    WorkingShiftIdSEPE,
    WorkingShiftIdActual,
    notes,
    status,
    ActualStartTime1,
    ActualEndTime1,
    ActualStartTime2,
    ActualEndTime2,
    TimeAttendanceId
)
SELECT 754, workingdate, 6, 0,0,0,0,0,0,0,0,0, 10149
FROM (
    SELECT '2022-09-10' AS workingdate UNION ALL
    SELECT '2022-09-11' UNION ALL
    SELECT '2022-11-20' -- 继续添加更多日期
) AS dates;

方法3：递归CTE生成连续日期（适合连续日期场景） #

如果要插入连续一段日期的数据，比如从2022-09-10到2022-09-30，用递归CTE自动生成日期范围，无需逐个手动输入：

MySQL 8.0+ / SQL Server版本：

WITH date_range AS (
    SELECT '2022-09-10' AS workingdate
    UNION ALL
    SELECT DATE_ADD(workingdate, INTERVAL 1 DAY) -- SQL Server替换为DATEADD(day, 1, workingdate)
    FROM date_range
    WHERE workingdate < '2022-09-11' -- 结束日期，会包含该日期
)
insert into WorkingSchedule(
    employeeid,
    workingdate,
    DepartmentId,
    WorkingShiftIdForecast,
    WorkingShiftIdSEPE,
    WorkingShiftIdActual,
    notes,
    status,
    ActualStartTime1,
    ActualEndTime1,
    ActualStartTime2,
    ActualEndTime2,
    TimeAttendanceId
)
SELECT 754, workingdate, 6, 0,0,0,0,0,0,0,0,0, 10149
FROM date_range;

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