SQL 批量插入存儲過程範例：WHILE 迴圈 + WAITFOR DELAY 定時寫入時間維度表

完整代碼

DECLARE @MyCounter INT;
SET @MyCounter = 0;              -- 初始化計數器
WHILE (@MyCounter < 2)           -- 迴圈 2 次
BEGIN
    WAITFOR DELAY '00:00:10';    -- 延遲 10 秒
    INSERT INTO time_by_day
    (time_id, the_date, the_year, month_of_year, quarter, day_of_month)
    SELECT TOP 1
        time_id + 1 AS time_id,
        the_date + 1 AS the_date,
        YEAR(the_date + 1) AS the_year,
        MONTH(the_date + 1) AS month_of_year,
        DATEPART(QUARTER, the_date + 1) AS quarter,
        DAY(the_date + 1) AS day_of_month
    FROM time_by_day
    ORDER BY time_id DESC;
    SET @MyCounter = @MyCounter + 1;
END

關鍵點解析

• WAITFOR DELAY 實現定時批次插入（每 10 秒一筆）。
• 子查詢取最新一行 (TOP 1 ... ORDER BY time_id DESC) 計算下一行值。
• 日期運算使用 YEAR()、MONTH()、DAY()、DATEPART(QUARTER,...)。
• 適用於時間維度表初始化、定時數據採集等場景。

移植提示

MySQL 中對應寫法需用 WHILE ... DO ... END WHILE、SLEEP(10)、INSERT ... SELECT ... ORDER BY ... LIMIT 1；變量用 @var。