🧾【教學】如何安全又穩定地使用 BULK INSERT 匯入大型 CSV / TSV 到 SQL Server？常見錯誤與解法全解析！

📌 前言：為什麼我們選擇 BULK INSERT？

在企業中常見的情境是，某些部門會匯出成千上萬筆資料的 CSV 或 TSV 檔案，而我們需要快速地匯入到 SQL Server 中進行分析或報表製作。這時候，BULK INSERT 是一個快速又有效率的選項。

---

🧱 Step 1：先建立「中繼表」接收原始資料

為了避免型別錯誤，我們建立一個 staging table，所有欄位都先用 nvarchar(4000)：

CREATE TABLE dbo.MyData_stg (
    ID            nvarchar(4000),
    Name          nvarchar(4000),
    Score         nvarchar(4000),
    CreateDate    nvarchar(4000)
);

這樣就不會因為欄位格式不合而導致匯入失敗。

---

🧾 Step 2：CSV ➜ TSV，避免欄位中有逗號破壞欄位格式

很多 CSV 欄位中含有逗號，會導致欄位錯位。可使用 PowerShell 轉成 Tab 分隔檔（TSV）：

$src = "D:\Data\source.csv"
$dst = "D:\Data\converted.tsv"

# 若資料夾不存在，先建立
New-Item -Path "D:\Data" -ItemType Directory -Force

Import-Csv $src | Export-Csv $dst -Delimiter "`t" -NoTypeInformation -Encoding UTF8

---

🧮 Step 3：使用 BULK INSERT 匯入 TSV

BULK INSERT dbo.MyData_stg
FROM 'D:\Data\converted.tsv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n',
    CODEPAGE = '65001',
    DATAFILETYPE = 'char',
    TABLOCK
);

常見錯誤解釋：

錯誤訊息編號	說明
4860	找不到檔案或沒有權限。請確認 SQL Server 使用者是否有權限讀該目錄。
4866	某一列資料過長，可能是欄位未正確分隔。請確認 FIELDTERMINATOR 與檔案格式一致。
4863	資料轉型失敗（例如數字欄位塞進字串）。
7330 / 7399	BULK 內部錯誤，多與檔案錯誤或權限有關。

---

🧪 Step 4：清理字串 + 正式轉型插入

插入正式表之前，先轉換資料型態並忽略空字串：

INSERT INTO dbo.MyData (ID, Name, Score, CreateDate)
SELECT
    TRY_CAST(NULLIF(LTRIM(RTRIM(ID)), '') AS int),
    NULLIF(Name, ''),
    TRY_CAST(NULLIF(Score, '') AS decimal(10, 2)),
    TRY_CAST(NULLIF(CreateDate, '') AS datetime)
FROM dbo.MyData_stg;

---

🛡️ 實戰小技巧總整理

技巧	說明
❗ 避免中文路徑	Windows 有時對 Unicode 支援不完整，請盡量使用英文資料夾名
✅ 優先用 TSV 格式	Tab 字元不容易出現在內容中，穩定性較高
🔐 給 SQL Server 權限	確保 MSSQLSERVER 帳號對目標路徑有「讀取」權限
🧪 LTRIM + RTRIM	去除前後空白，避免轉型失敗
🧹 NULLIF('','')	空字串轉為 NULL，方便後續處理

---

🧩 附錄：適合放檔案的安全路徑

建議將 CSV / TSV 放在 SQL Server 能夠讀取的資料夾中，例如：

• C:\Import\

• D:\DataLoad\

• 建立共用資料夾並確認帳號有存取權限

---

🧠 結語

BULK INSERT 雖然強大，但常因格式問題出錯。透過本文教學，搭配 staging table 和 PowerShell 預處理，能有效提升匯入成功率，讓你的資料 ETL 更穩定！