萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server BCP 實現二進位檔案(image/text/ntext)高效導入導出

SQL Server 原生 bcp 工具支援 image、text、ntext 大型物件(LOB)資料的高速批量匯入匯出。本文提供一個封裝完整邏輯的預存程序 p_binaryIO,可直接呼叫完成單欄位 LOB 資料的檔案級讀寫。

適用場景

  • image:二進位檔案(圖片、PDF、Office 文件等)。
  • text/ntext:長文本資料(日誌、XML、JSON 等)。

預存程序介面

CREATE PROC p_binaryIO
  @servename varchar(30),   -- 伺服器名稱
  @username  varchar(30),   -- 登入帳號
  @password  varchar(30),   -- 密碼
  @tbname    varchar(500),  -- 資料庫..表名(如 db..tbl)
  @fdname    varchar(30),   -- 目標欄位名
  @fname     varchar(1000), -- 完整檔案路徑(含副檔名)
  @tj        varchar(1000) = '', -- WHERE 條件(含表前綴)
  @isout     bit = 1        -- 1=導出(預設), 0=導入
AS ...

呼叫範例

-- 導出:將符合條件的第一行 img 欄位寫入 c:\zj1.dat
EXEC p_binaryIO 'localhost','sa','123','mydb..tb','img','c:\zj1.dat';

-- 導入:將 c:\zj1.dat 內容寫回符合條件的所有行 img 欄位
EXEC p_binaryIO 'localhost','sa','123','mydb..tb','img','c:\zj1.dat','',0;

核心實作流程

  1. 導出:
    • 建立臨時表 ##temp_GUID,寫入單行格式檔資訊(型別、前綴、長度、結束、格式)。
    • bcp ... queryout 產生格式檔 _temp。
    • bcp "SELECT TOP 1 fd FROM tb WHERE ..." queryout file /i format_file 導出實際資料。
  2. 導入:
    • SELECT TOP 0 fd INTO ##temp FROM tb 建立空結構臨時表。
    • bcp ##temp in file /i format_file 將檔案資料載入臨時表。
    • UPDATE tb SET fd = t.fd FROM tb a JOIN ##temp t ON ... WHERE ... 回寫目標表。
  3. 清理:刪除臨時表、格式檔。

關鍵技術細節

  • 使用 xp_cmdshell 呼叫 bcp.exe,需啟用 xp_cmdshell 並具備 sysadmin 權限。
  • 格式檔(/i)記錄欄位型別、長度、終止符,確保 LOB 資料邊界正確。
  • 導入前透過 xp_getfiledetails 取得檔案大小,寫入格式檔「長度」欄位,防止截斷。
  • 警告: 導入會覆蓋符合條件的所有行;導出會輸出所有符合條件的行(範例僅取 TOP 1)。

現代替代方案

SQL Server 2008+ 建議改用 FILESTREAM 或 FileTable 管理非結構化資料,配合 BACKUP/RESTORE 或 INSERT ... SELECT FROM OPENROWSET(BULK...) 獲得更佳維運體驗與 T-SQL 原生支援。

copyright © 萬盛學電腦網 all rights reserved