萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL 批次插入資料的多種實作技巧

應用場景

在 BI 系統效能測試中,需快速產生大量測試資料至事實表與維度表。本文整理四種常見的批次插入寫法,適用於 SQL Server 環境。

1. 單筆 INSERT(基礎寫法)

INSERT INTO time_by_day
    (time_id, the_date, the_year, month_of_year, quarter, day_of_month)
VALUES
    ('1101', '1999-10-01', '1999', '10', 'Q4', '1');

2. 遞迴單筆插入(根據上一筆推算下一筆)

INSERT INTO time_by_day
    (time_id, the_date, the_year, month_of_year, quarter, day_of_month)
SELECT TOP 1
    time_id + 1,
    the_date + 1,
    YEAR(the_date + 1),
    MONTH(the_date + 1),
    QUARTER(the_date + 1),
    DAY(the_date + 1)
FROM time_by_day
ORDER BY time_id DESC;

3. WHILE 迴圈批次插入(含延遲控制)

DECLARE @MyCounter INT = 0;

WHILE (@MyCounter < 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,
        the_date + 1,
        YEAR(the_date + 1),
        MONTH(the_date + 1),
        QUARTER(the_date + 1),
        DAY(the_date + 1)
    FROM time_by_day
    ORDER BY time_id DESC;

    SET @MyCounter = @MyCounter + 1;
END;

4. 以日期變數控制的大量插入(20 萬筆範例)

DECLARE @MyCounter INT = 0;
DECLARE @the_date DATETIME = '1999-01-04';

WHILE (@MyCounter < 200000)
BEGIN
    WAITFOR DELAY '00:00:10';

    INSERT INTO time_by_day (time_id, the_date)
    VALUES ('371', @the_date); -- 實務上 time_id 應配合自增或序列

    SET @the_date = DATEADD(DAY, 1, @the_date);
    SET @MyCounter = @MyCounter + 1;
END;

效能建議

  • 大量資料建議使用 BULK INSERT、bcp 或 SqlBulkCopy(.NET),效能遠優於迴圈 INSERT。
  • 測試環境中可透過 WAITFOR DELAY 模擬真實負載間隔。
  • 維度表資料量較小時,上述寫法足夠;事實表百萬級以上請改用批次工具。
copyright © 萬盛學電腦網 all rights reserved