使用 T-SQL 批次匯入目錄下所有檔案到 SQL Server (BULK INSERT / BCP)

需求：定期將特定目錄下的多個 CSV/文字檔一次性匯入資料庫。本文提供兩種純 T-SQL 方案：xp_cmdshell + BULK INSERT (資料庫端執行) 與 xp_cmdshell + BCP.exe (客戶端工具)，並內建匯入記錄功能。

測試環境建立

CREATE DATABASE Bank; GO
USE Bank; GO
CREATE TABLE Account (
    ID INT, Name VARCHAR(100), AccountNo VARCHAR(100), Balance MONEY
);
CREATE TABLE LogTable (
    ID INT IDENTITY(1,1), Query VARCHAR(1000), ImportedDate DATETIME DEFAULT GETDATE()
);
-- 建立測試檔案 C:\MyImport\a.csv, b.csv, c.csv (內容略)

方案一：BULK INSERT (推薦，效能最佳)

前提：SQL Server 服務帳號對檔案目錄具讀取權限；檔案需可從伺服器端路徑存取 (本機路徑或 UNC 路徑)。

CREATE PROCEDURE usp_ImportMultipleFiles
    @FilePath VARCHAR(500),     -- 目錄路徑，結尾需有 \
    @Pattern VARCHAR(100),      -- 檔案模式 *.csv
    @TableName VARCHAR(128)     -- 目標資料表
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Query VARCHAR(1000), @MaxID INT, @Cnt INT = 0, @FileName VARCHAR(100);
    
    -- 1. 取得檔案清單
    CREATE TABLE #Files (Name VARCHAR(200));
    INSERT #Files EXEC master.dbo.xp_cmdshell 'dir "' + @FilePath + @Pattern + '" /b';
    DELETE #Files WHERE Name IS NULL OR Name NOT LIKE '%.%';
    
    -- 2. 加上流水號
    SELECT IDENTITY(INT,1,1) AS ID, Name INTO #Seq FROM #Files;
    DROP TABLE #Files;
    SELECT @MaxID = MAX(ID) FROM #Seq;
    
    -- 3. 迴圈匯入
    WHILE @Cnt < @MaxID
    BEGIN
        SET @Cnt += 1;
        SELECT @FileName = Name FROM #Seq WHERE ID = @Cnt;
        
        SET @Query = 'BULK INSERT ' + QUOTENAME(@TableName) + 
            ' FROM "' + @FilePath + @FileName + '"' +
            ' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', FIRSTROW = 1)';
        
        EXEC (@Query);
        INSERT LogTable (Query) VALUES (@Query);  -- 記錄匯入指令
    END
    DROP TABLE #Seq;
END
GO

-- 執行範例
EXEC usp_ImportMultipleFiles 'C:\MyImport\', '*.csv', 'Account';
EXEC usp_ImportMultipleFiles 'C:\MyImport\', '*.*', 'Account';

方案二：BCP.exe (適用跨伺服器/無 BULK INSERT 權限)

前提：SQL Server Agent 服務帳號具檔案目錄與目標伺服器存取權；BCP.exe 需在伺服器路徑中。

CREATE PROCEDURE usp_ImportMultipleFilesBCP
    @ServerName VARCHAR(128), @DatabaseName VARCHAR(128),
    @FilePath VARCHAR(500), @Pattern VARCHAR(100), @TableName VARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Query VARCHAR(2000), @MaxID INT, @Cnt INT = 0, @FileName VARCHAR(100);
    
    CREATE TABLE #Files (Name VARCHAR(200));
    INSERT #Files EXEC master.dbo.xp_cmdshell 'dir "' + @FilePath + @Pattern + '" /b';
    DELETE #Files WHERE Name IS NULL OR Name NOT LIKE '%.%';
    SELECT IDENTITY(INT,1,1) AS ID, Name INTO #Seq FROM #Files;
    DROP TABLE #Files;
    SELECT @MaxID = MAX(ID) FROM #Seq;
    
    WHILE @Cnt < @MaxID
    BEGIN
        SET @Cnt += 1;
        SELECT @FileName = Name FROM #Seq WHERE ID = @Cnt;
        
        -- BCP 參數：-T 信任連線 -c 字元模式 -r\n 列結束 -t, 欄位分隔
        SET @Query = 'bcp "' + @DatabaseName + '.dbo.' + @TableName + '" in "' 
            + @FilePath + @FileName + '" -S' + @ServerName + ' -T -c -r\n -t,';
        
        SET @Query = 'master.dbo.xp_cmdshell ' + QUOTENAME(@Query, '''');
        EXEC (@Query);
        INSERT LogTable (Query) VALUES (@Query);
    END
    DROP TABLE #Seq;
END
GO

關鍵權限與安全性設定

進階擴充建議

• 錯誤處理：在迴圈中加入 TRY...CATCH 記錄失敗檔案並繼續下一個。
• 檔案移動/封存：匯入成功後呼叫 xp_cmdshell 'move ...' 移至 Processed 子目錄。
• 平行處理：使用 Service Broker 或 SQL Agent 多步驟作業並行處理多個子目錄。
• 格式檔：複雜檔案格式 (固定寬度、跳過欄位) 使用 FORMATFILE 選項。

現代替代方案 (SQL 2016+)

• OPENROWSET(BULK...)：無需 xp_cmdshell，直接在查詢中讀取檔案。
• PolyBase / 外部資料表：查詢 Azure Blob/HDFS/CSV 如同資料表。
• SSIS / Azure Data Factory：企業級 ETL 排程、監控、錯誤處理框架。

純 T-SQL 方案適合輕量級、無 ETL 工具的環境；生產環境建議導入 SSIS/ADF 獲得完整的資料血統、重跑與監控能力。

1. 上一篇文章： 修改使用者定義資料類型精度的完整解決方案
2. 下一篇文章： 建立安全的 MSSQL Server 服務啟動帳號：最小權限實戰指南

關於SqlServer

SQL Server 複寫監控與資料庫郵件告警實作

業務背景 機房遷移期間，需將資訊庫從機房 A 即時複寫至機房

SQL Server 2008 使用 DataTable 作為預存程序參數的實作教學

背景與優勢 在 SQL Server 2000 時代，若要將

SQL Server 2005 徹底移除指南：命令列安靜解除安裝與殘留清理

前言：為什麼「新增/移除程式」不夠？ 透過控制台「程式和功能

DevExpress XtraPivotGrid 實戰：快速構建 OLAP 多維數據透視報表

前置準備 安裝 Developer Express .NE