SQL Server BULK INSERT 大數據導入全參數詳解與實戰範例

BULK INSERT 是 SQL Server 將外部數據文件高效批量載入表/視圖的核心命令，性能遠超逐行 INSERT。本文完整梳理所有參數語法、核心參數場景解析，並給出生產級範例模板。

完整語法結構

BULK INSERT [database_name.[schema_name].|schema_name.][table_name|view_name]
FROM 'data_file'
WITH (
    [BATCHSIZE = batch_size],           -- 每批行數，每批一事務
    [CHECK_CONSTRAINTS],                -- 檢查約束（預設忽略）
    [CODEPAGE = 'ACP'|'OEM'|'RAW'|code_page], -- 字符編碼頁
    [DATAFILETYPE = 'char'|'native'|'widechar'|'widenative'], -- 文件格式
    [FIELDTERMINATOR = 'field_terminator'], -- 字段分隔符（預設 \t）
    [FIRSTROW = first_row],             -- 起始行號（預設 1）
    [FIRE_TRIGGERS],                    -- 觸發 INSERT 觸發器
    [FORMATFILE = 'format_file_path'],  -- 格式化文件（bcp 生成）
    [KEEPIDENTITY],                     -- 保留文件中的標識列值
    [KEEPNULLS],                        -- 保留空值而非默認值
    [KILOBYTES_PER_BATCH = kb],         -- 每批近似 KB 數
    [LASTROW = last_row],               -- 結束行號（預設 0=末尾）
    [MAXERRORS = max_errors],           -- 最大錯誤容忍（預設 0）
    [ORDER (column [ASC|DESC] ...)],    -- 數據文件排序順序（匹配聚簇索引提速）
    [ROWS_PER_BATCH = rows_per_batch],  -- 每批行數（無 BATCHSIZE 時用）
    [ROWTERMINATOR = 'row_terminator'], -- 行分隔符（預設 \n）
    [TABLOCK],                          -- 表級鎖（減少鎖升級，大幅提速）
    [ERRORFILE = 'file_name']           -- 錯誤行導出文件
);

核心參數場景解析

生產級範例模板

範例 1：標準 CSV 導入（含表頭，管道符分隔，指定編碼）

BULK INSERT Northwind.dbo.[Order Details]
FROM 'F:/orders/lineitem.tbl'
WITH (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '|\n',
    FIRSTROW = 2,              -- 跳過標題行
    CODEPAGE = '65001',        -- UTF-8
    TABLOCK,                   -- 表鎖提速
    BATCHSIZE = 5000,
    MAXERRORS = 100,
    ERRORFILE = 'F:/orders/lineitem_err.log'
);

範例 2：最小權限、觸發器生效、保留標識列

BULK INSERT xsxt.dbo.tabletest
FROM 'C:/data.txt'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRE_TRIGGERS,
    KEEPIDENTITY,
    CHECK_CONSTRAINTS,
    TABLOCK
);

範例 3：使用格式化文件應對列順序不一致

-- 1. 用 bcp 生成格式文件
-- bcp db.dbo.table format nul -c -t, -r\n -f fmt.xml -T

-- 2. 導入時引用
BULK INSERT db.dbo.table
FROM 'data.csv'
WITH (FORMATFILE = 'C:/fmt/fmt.xml', FIRSTROW=2, TABLOCK);

權限與最佳實踐

• 權限：僅 sysadmin 和 bulkadmin 固定服務器角色可執行。
• 事務控制：可包裹在用戶事務中；若指定 BATCHSIZE，回滾會回滾所有已提交批次。
• 日誌模式：配合 TABLOCK + BULK_LOGGED 恢復模式可最小化日誌量。
• 文件路徑：必須是 SQL Server 服務器視角的本地路徑或 UNC 路徑（\\Server\Share\file.txt）。

BULK INSERT 是 ETL、數據遷移、初始化載入的利器，掌握參數組合拳，導入速度可提升 10-100 倍。