SQL Server BCP 雙向實戰：二進位檔案（圖片/文件）匯出與匯入完整範例

BCP (Bulk Copy Program) 是 SQL Server 高效能大量複製工具，支援 **image、text、ntext** 等大型物件（LOB）欄位的檔案級匯出入。本文提供一個封裝良好的預存程序 p_binaryIO，實現單指令雙向操作，並解析關鍵邏輯與呼叫範例。

預存程式碼完整版

CREATE PROC p_binaryIO
    @servename VARCHAR(30),   -- 伺服器名稱
    @username  VARCHAR(30),   -- 登入帳號
    @password  VARCHAR(30),   -- 密碼
    @tbname    VARCHAR(500),  -- 資料庫..資料表名
    @fdname    VARCHAR(30),   -- 目標欄位名
    @fname     VARCHAR(1000), -- 完整檔案路徑（處理中會產生 .bak）
    @tj        VARCHAR(1000)='', -- WHERE 條件（匯入時若含目標欄位需加表前綴）
    @isout     BIT=1          -- 1=匯出(預設), 0=匯入
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @fname_in VARCHAR(1000),  -- 格式檔路徑
            @fsize  VARCHAR(20),      -- 檔案大小
            @m_tbname VARCHAR(50),    -- 暫存格式表名
            @sql NVARCHAR(MAX);

    /*--- 取得匯入檔案大小 ---*/
    IF @isout = 1 SET @fsize = '0';
    ELSE BEGIN
        CREATE TABLE #tb (fname VARCHAR(20), fsize INT, ...);
        INSERT #tb EXEC master..xp_getfiledetails @fname;
        SELECT @fsize = fsize FROM #tb;
        DROP TABLE #tb;
        IF @fsize IS NULL BEGIN PRINT '檔案不存在'; RETURN; END
    END

    /*--- 產生 BCP 格式檔 ---*/
    SET @m_tbname = N'[##temp' + CAST(NEWID() AS VARCHAR(40)) + N']';
    SET @sql = N'SELECT * INTO ' + @m_tbname + N' FROM (
        SELECT NULL AS [類型]
        UNION ALL SELECT 0 AS [前綴]
        UNION ALL SELECT ' + @fsize + N' AS [長度]
        UNION ALL SELECT NULL AS [結束]
        UNION ALL SELECT NULL AS [格式]
    ) a';
    EXEC(@sql);

    SET @fname_in = @fname + N'_temp';
    SET @sql = N'bcp "' + @m_tbname + N'" out "' + @fname_in + N'" /S"' + @servename + N'"' +
               CASE WHEN ISNULL(@username,'')='' THEN N'' ELSE N' /U"' + @username + N'"' END +
               N' /P"' + ISNULL(@password,'') + N'" /c';
    EXEC master..xp_cmdshell @sql;
    SET @sql = N'DROP TABLE ' + @m_tbname; EXEC(@sql);

    /*--- 匯出 ---*/
    IF @isout = 1 BEGIN
        SET @sql = N'bcp "SELECT TOP 1 ' + @fdname + N' FROM ' + @tbname +
                   CASE WHEN ISNULL(@tj,'')='' THEN N'' ELSE N' WHERE ' + @tj END +
                   N'" QUERYOUT "' + @fname + N'" /S"' + @servename + N'"' +
                   CASE WHEN ISNULL(@username,'')='' THEN N'' ELSE N' /U"' + @username + N'"' END +
                   N' /P"' + ISNULL(@password,'') + N'" /i"' + @fname_in + N'"';
        EXEC master..xp_cmdshell @sql;
    END
    /*--- 匯入 ---*/
    ELSE BEGIN
        SET @sql = N'SELECT TOP 0 ' + @fdname + N' INTO ' + @m_tbname + N' FROM ' + @tbname; EXEC(@sql);
        SET @sql = N'bcp "' + @m_tbname + N'" IN "' + @fname + N'" /S"' + @servename + N'"' +
                   CASE WHEN ISNULL(@username,'')='' THEN N'' ELSE N' /U"' + @username + N'"' END +
                   N' /P"' + ISNULL(@password,'') + N'" /i"' + @fname_in + N'"';
        EXEC master..xp_cmdshell @sql;

        SET @sql = N'UPDATE ' + @tbname + N' SET ' + @fdname + N'=b.' + @fdname +
                   N' FROM ' + @tbname + N' a, ' + @m_tbname + N' b' +
                   CASE WHEN ISNULL(@tj,'')='' THEN N'' ELSE N' WHERE ' + @tj END;
        EXEC(@sql);
        SET @sql = N'DROP TABLE ' + @m_tbname; EXEC(@sql);
    END

    /*--- 清理格式檔 ---*/
    SET @sql = N'DEL ' + @fname_in; EXEC master..xp_cmdshell @sql;
END

呼叫範例

匯出單一二進位欄位到檔案

EXEC p_binaryIO 
    'MyServer', 'sa', 'pwd', 
    'MyDB.dbo.Documents', 'FileContent', 
    'C:\Export\doc_001.pdf', '', 1;

從檔案匯入更新符合條件的所有列

EXEC p_binaryIO 
    'MyServer', 'sa', 'pwd', 
    'MyDB.dbo.Documents', 'FileContent', 
    'C:\Import\doc_001.pdf', 'DocID=100', 0;

關鍵技術細節解析

• 格式檔：BCP 需描述欄位型別、長度、結束符等資訊，程序動態建立暫存表並 bcp ... out ... /c 產生字元模式格式檔。
• xp_cmdshell：呼叫外部 bcp.exe，需啟用 sp_configure 'xp_cmdshell', 1，且執行帳號具備 OS 層檔案讀寫權限。
• 匯入邏輯：先建立空結構暫存表 → BCP 將檔案資料載入暫存表 → UPDATE ... FROM 將暫存表欄位值回寫目標表符合條件的列。
• 注意：@tj 條件將套用於**所有符合列**，匯入會覆蓋多筆記錄，使用時務必確認條件精確度。

現代替代方案

BCP 仍是大批量二進位資料遷移、異構匯出入的利器，善用 p_binaryIO 封裝可大幅降低操作複雜度。