SQL Server 大量資料高效插入：Table-Valued Function 與 BULK INSERT 兩大實戰方案

背景痛點

雙設備每秒寫入 4000 筆，單純 INSERT 僅能寫入 2800 筆，**遺失 1200 筆/秒**。經測試，以下兩種方案顯著改善。

方案一：自訂表值函數（TVF）批量解析字串

核心思路

1. 應用層將多筆資料組裝成單一字串：
   '111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|2222|3333|456,7894,7458|0|1|2014-01-01 12:15:16'
   — 行分隔符 `;`，欄位分隔符 `|`
2. TVF 解析字串 → 回傳資料表變數 → 單次 INSERT ... SELECT * FROM TVF(...)

關鍵程式碼（簡化版）

CREATE FUNCTION [dbo].[fun_BatchInsert]
(
    @str VARCHAR(MAX),
    @rowSplit CHAR(1),   -- ';'
    @colSplit CHAR(1)    -- '|'
)
RETURNS @t TABLE
(
    MaxValue FLOAT,
    Phase INT,
    SlopeValue FLOAT,
    Data VARCHAR(600),
    Alarm INT,
    AlmLev INT,
    GpsTime DATETIME,
    UpdateTime DATETIME
)
AS
BEGIN
    -- 雙層 WHILE 迴圈解析行與欄位，INSERT INTO @t
    -- 完整程式碼見原文
    RETURN
END

-- 呼叫範例
INSERT INTO [MyTable]
SELECT * FROM [dbo].[fun_BatchInsert](
    '111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;...',
    ';', '|'
)

優點：單次往返、減少網路往返與編譯開銷；缺點：函數邏輯複雜、維護成本高。

方案二：BULK INSERT / bcp 批量匯入（效能最強）

完整流程

1. 建立格式檔：
   EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student format nul -f C:/student_fmt.xml -x -c -T'
2. 匯出資料檔：
   EXEC master..xp_cmdshell 'BCP db_mgr.dbo.T_Student out C:/student.data -f C:/student_fmt.xml -T'
3. 清空目標表：TRUNCATE TABLE db_mgr.dbo.T_Student
4. 批量匯入：
   BULK INSERT db_mgr.dbo.T_Student FROM 'C:/student.data' WITH (FORMATFILE = 'C:/student_fmt.xml')

進階用法：OPENROWSET(BULK...)

-- 目標表需存在
INSERT INTO db_mgr.dbo.T_Student(F_Code, F_Name)
SELECT F_Code, F_Name
FROM OPENROWSET(BULK N'C:/student.data', FORMATFILE=N'C:/student_fmt.xml') AS t

-- 目標表可不存在（自動建表）
SELECT F_Code, F_Name INTO db_mgr.dbo.tt
FROM OPENROWSET(BULK N'C:/student.data', FORMATFILE=N'C:/student_fmt.xml') AS t

效能對比與選型建議

避坑提醒

• xp_cmdshell 預設關閉，啟用有安全風險，生產環境嚴格控管
• BULK INSERT 需目標表結構與格式檔完全一致
• 大量插入前考慮 TABLOCK、DROP INDEX 再重建、簡易復原模式