您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server BCP 大量資料匯出匯入完整指南與常見問題排查

編輯：關於SqlServer

SQL Server 匯出匯入方式比較

方式	優點	缺點	適用場景
SSMS 圖形介面精靈	直觀易用、支援查詢導出	大量資料極慢、記憶體佔用高	小資料量、臨時操作
T-SQL (SELECT INTO / INSERT SELECT)	無需外部工具	跨資料庫/伺服器受限、大量資料慢	同伺服器內部搬移
BCP 命令列工具	大量資料最快、支援平行、可腳本化	需命令列經驗、錯誤處理弱	GB/TB 級資料搬移、自動化排程
SSIS	企業級 ETL、視覺化、錯誤處理完善	學習曲線、部署相對複雜	生產環境固定 ETL 流程

BCP 核心語法與參數詳解

基本語法

bcp {table_name | query} {in | out | queryout | format} data_file
    [-S server_name] [-U login_id] [-P password]
    [-c | -n | -w | -N] [-t field_term] [-r row_term]
    [-b batch_size] [-a packet_size] [-k] [-E] [-h hints]

實戰範例


bcp MyDB.dbo.MyTable out C:\data\MyTable.txt -S "MyServer" -U "sa" -P "pwd" -c


bcp "SELECT Id, Name, CreateDate FROM MyDB.dbo.MyTable WHERE Status=1" queryout C:\data\MyTable.txt -S MyServer -U sa -P pwd -c


bcp "SELECT * FROM MyDB.dbo.MyTable" queryout C:\data\MyTable.csv -S MyServer -U sa -P pwd -c -t"," -r"\n"


bcp "SELECT * FROM MyDB.dbo.MyTable" queryout C:\data\MyTable.txt -S MyServer -U sa -P pwd -c -t"," -r"\n" -k -b5000 -a65535


EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyDB.dbo.MyTable" queryout C:\data\MyTable.txt -S MyServer -U "sa" -P "pwd" -c'


EXEC master..xp_cmdshell 'bcp MyDB.dbo.MyTable in C:\data\MyTable.txt -c -T'


EXEC master..xp_cmdshell 'bcp MyDB.dbo.MyTable in C:\data\MyTable.txt -c -F 10 -L 13 -T'

常見問題與解決方案

問題 1：Null 值匯出時想替換為特定文字（如 'NULL'）

方案：在查詢中使用 ISNULL(col, 'NULL') 或 COALESCE(col, 'NULL')；或匯出後用腳本後處理。

問題 2：CASE WHEN 導致命令超過 1023 位元組限制

原因：xp_cmdshell 命令字串有長度限制。

方案：

將複雜查詢封裝為檢視表或儲存程序，BCP 呼叫簡單的 SELECT * FROM vw_ExportData
使用格式化檔案 (-f format.fmt) 定義欄位對應，簡化命令列
改用 SSIS 或 SQLCLR 繞過命令列長度限制

問題 3：大量資料匯出報錯「查詢提示超過最大目錄緩衝區 1023 位元組」

同問題 2，核心在於 xp_cmdshell 命令字串長度。不建議使用暫存表/中繼表繞過（幾十 GB 資料搬移不現實）。

進階技巧

格式化檔案：bcp MyDB.dbo.MyTable format nul -c -f MyTable.fmt -T 產生 .fmt 檔，後續匯入匯出指定 -f MyTable.fmt，支援欄位跳過、順序調整、型別轉換
保留身分識別值：匯入時加 -E 參數保留來源 Identity 值
只讀/唯寫鎖定提示：-h "TABLOCK" 提升大量匯入效能
錯誤檔案：-e err.log 記錄匯入錯誤列