如何仅在工作日向SQL表批量插入重复结构数据？

Absolutely! You can definitely pull this off. The key is to generate a range of future dates, filter out weekends, then batch insert records using your fixed values from the sample data. Below are step-by-step solutions for the most popular SQL databases:

MySQL Solution #

We'll use a recursive CTE to generate dates, filter for workdays (Monday-Friday), then cross-join with your two time slots to create the required records:

WITH RECURSIVE date_range AS (
    -- Start with today's date
    SELECT CURDATE() AS workday
    UNION ALL
    SELECT DATE_ADD(workday, INTERVAL 1 DAY)
    FROM date_range
    -- Stop at today + 2 years
    WHERE workday < DATE_ADD(CURDATE(), INTERVAL 2 YEAR)
),
workdays_only AS (
    SELECT workday
    FROM date_range
    -- WEEKDAY() returns 0=Monday, 4=Friday
    WHERE WEEKDAY(workday) BETWEEN 0 AND 4
),
time_slots AS (
    SELECT '08:00:00' AS start_time, '10:00:00' AS end_time
    UNION ALL
    SELECT '15:00:00' AS start_time, '17:00:00' AS end_time
)
INSERT INTO your_table_name (info, user, start_date, end_date, resource_id)
SELECT 
    NULL AS info,
    'aorlik' AS user,
    CONCAT(workday, ' ', start_time) AS start_date,
    CONCAT(workday, ' ', end_time) AS end_date,
    1 AS resource_id
FROM workdays_only
CROSS JOIN time_slots;

Notes for MySQL: #

• Replace your_table_name with your actual table name.
• If id is an auto-incrementing primary key, you don't need to include it in the INSERT statement—MySQL will handle it automatically.

PostgreSQL Solution #

PostgreSQL has a built-in generate_series function to simplify date range creation:

WITH date_range AS (
    SELECT generate_series(
        CURRENT_DATE,
        CURRENT_DATE + INTERVAL '2 years',
        INTERVAL '1 day'
    )::DATE AS workday
),
workdays_only AS (
    SELECT workday
    FROM date_range
    -- ISODOW returns 1=Monday, 5=Friday
    WHERE EXTRACT(ISODOW FROM workday) BETWEEN 1 AND 5
),
time_slots AS (
    SELECT '08:00:00'::TIME AS start_time, '10:00:00'::TIME AS end_time
    UNION ALL
    SELECT '15:00:00'::TIME AS start_time, '17:00:00'::TIME AS end_time
)
INSERT INTO your_table_name (info, user, start_date, end_date, resource_id)
SELECT 
    NULL AS info,
    'aorlik' AS user,
    workday + start_time AS start_date,
    workday + end_time AS end_date,
    1 AS resource_id
FROM workdays_only
CROSS JOIN time_slots;

Notes for PostgreSQL: #

• The ::DATE and ::TIME casts ensure proper type handling when combining dates and times.
• Again, auto-incrementing id columns don't need to be specified in the INSERT.

SQL Server Solution #

We'll use a recursive CTE for date generation, and ISOWEEKDAY to reliably filter workdays (avoids issues with server settings that treat Sunday as the first day of the week):

WITH date_range AS (
    SELECT CAST(GETDATE() AS DATE) AS workday
    UNION ALL
    SELECT DATEADD(DAY, 1, workday)
    FROM date_range
    WHERE workday < DATEADD(YEAR, 2, CAST(GETDATE() AS DATE))
),
workdays_only AS (
    SELECT workday
    FROM date_range
    -- ISOWEEKDAY returns 1=Monday, 5=Friday
    WHERE DATEPART(ISOWEEKDAY, workday) BETWEEN 1 AND 5
),
time_slots AS (
    SELECT CAST('08:00:00' AS TIME) AS start_time, CAST('10:00:00' AS TIME) AS end_time
    UNION ALL
    SELECT CAST('15:00:00' AS TIME) AS start_time, CAST('17:00:00' AS TIME) AS end_time
)
INSERT INTO your_table_name (info, user, start_date, end_date, resource_id)
SELECT 
    NULL AS info,
    'aorlik' AS user,
    CAST(workday AS DATETIME) + start_time AS start_date,
    CAST(workday AS DATETIME) + end_time AS end_date,
    1 AS resource_id
FROM workdays_only
CROSS JOIN time_slots
OPTION (MAXRECURSION 0); -- Required for ranges longer than 100 days

Notes for SQL Server: #

• The OPTION (MAXRECURSION 0) is necessary because a 2-year range has over 700 days, exceeding the default recursion limit of 100.

Bonus: Excluding Holidays (Optional) #

If you need to exclude public holidays, create a separate holidays table with a holiday_date column, then modify the workdays_only CTE to add:

WHERE ...
AND NOT EXISTS (SELECT 1 FROM holidays WHERE holiday_date = workday)

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