在 BI 系統效能測試中,需快速產生大量測試資料至事實表與維度表。本文整理四種常見的批次插入寫法,適用於 SQL Server 環境。
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');
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;
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;
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 模擬真實負載間隔。