T-SQL 批次匯入目錄下多個檔案到 SQL Server（xp_cmdshell + BULK INSERT / BCP）

測試環境建立

-- 目錄 C:\MyImport\ 下三個 CSV
-- a.csv: 1, MAK, A9411792711, 3400.25 ...
-- b.csv: 11, Rubon, 9671792711, 400.14 ...
-- c.csv: 69, Lucy, 8411992710, 305.11 ...

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());

方法一：xp_cmdshell + BULK INSERT

CREATE PROCEDURE usp_ImportMultipleFiles
    @filepath varchar(500), @pattern varchar(100), @TableName varchar(128)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @query varchar(1000), @max1 int, @count1 int = 0, @filename varchar(100);

    CREATE TABLE #x (name varchar(200));
    SET @query = 'master.dbo.xp_cmdshell "dir ' + @filepath + @pattern + ' /b"';
    INSERT #x EXEC (@query);
    DELETE #x WHERE name IS NULL;

    SELECT IDENTITY(int,1,1) AS ID, name INTO #y FROM #x;
    DROP TABLE #x;
    SET @max1 = (SELECT MAX(ID) FROM #y);

    WHILE @count1 <= @max1
    BEGIN
        SET @count1 = @count1 + 1;
        SELECT @filename = name FROM #y WHERE ID = @count1;
        SET @query = 'BULK INSERT ' + @TableName + ' FROM "' + @filepath + @filename + '" WITH (FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")';
        EXEC (@query);
        INSERT logtable (Query) VALUES (@query);
    END
    DROP TABLE #y;
END;
GO

呼叫：EXEC usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

方法二：xp_cmdshell + BCP 公用程式

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(1000), @max1 int, @count1 int = 0, @filename varchar(100);

    CREATE TABLE #x (name varchar(200));
    SET @query = 'master.dbo.xp_cmdshell "dir ' + @filepath + @pattern + ' /b"';
    INSERT #x EXEC (@query);
    DELETE #x WHERE name IS NULL;

    SELECT IDENTITY(int,1,1) AS ID, name INTO #y FROM #x;
    DROP TABLE #x;
    SET @max1 = (SELECT MAX(ID) FROM #y);

    WHILE @count1 <= @max1
    BEGIN
        SET @count1 = @count1 + 1;
        SELECT @filename = name FROM #y WHERE ID = @count1;
        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 #y;
END;
GO

呼叫：EXEC usp_ImportMultipleFilesBCP 'SERVERNAME', 'Bank', 'c:\myimport\', '*.csv', 'Account'

前置條件與安全性

• 啟用 xp_cmdshell：EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
• SQL Server 服務帳戶需具備目錄讀取權限與 BCP 執行權限。
• 生產環境建議改用 SSIS 或 外部 ETL 工具，避免在資料庫引擎層呼叫 Shell。
• BCP 參數說明：-T 信任連線、-c 字元模式、-r\n 列結束符、-t, 欄位分隔符。