将指定文件夹中同结构CSV递归导入SQL Server并新增文件名列

我明白你现在的需求——把C:\Data\下所有Schema一致的CSV文件（包括子文件夹里的）导入到同一张SQL Server表，还要给每条记录加上对应的来源文件名。之前用xp_cmdShell的方法没生效，大概率是没处理递归遍历，也没在导入时带上文件名信息，我给你一套完整的可行方案：

1. 前期准备 #

1.1 创建存储文件列表的临时表 #

先建个临时表来存放所有要处理的CSV文件路径和文件名：

IF OBJECT_ID('tempdb..#AllCSVFiles') IS NOT NULL DROP TABLE #AllCSVFiles;
CREATE TABLE #AllCSVFiles (
    FilePath NVARCHAR(255),
    FileName NVARCHAR(100)
);

1.2 确保目标表符合要求 #

假设你的CSV包含Col1,Col2,Col3这类列，目标表需要额外添加SourceFileName列来存储来源文件名，要和CSV的字段类型匹配：

IF OBJECT_ID('dbo.TargetTable') IS NULL
CREATE TABLE dbo.TargetTable (
    Col1 VARCHAR(50),
    Col2 INT,
    Col3 DATETIME,
    SourceFileName NVARCHAR(100) -- 新增的来源文件名列
);

2. 递归获取所有CSV文件路径 #

之前的dir命令没加/s参数，所以没法遍历子文件夹。现在用带/s的命令递归获取所有CSV，再清理无效行、提取纯文件名：

DECLARE @Path NVARCHAR(255) = 'C:\Data\';
DECLARE @Cmd NVARCHAR(500) = 'dir "' + @Path + '*.csv" /s /b';

-- 把文件路径插入临时表，过滤xp_cmdShell返回的空行或错误行
INSERT INTO #AllCSVFiles (FilePath)
EXEC master..xp_cmdShell @Cmd;

-- 清理无效数据，提取文件名
DELETE FROM #AllCSVFiles WHERE FilePath IS NULL OR FilePath LIKE '%File Not Found%';
UPDATE #AllCSVFiles 
SET FileName = REVERSE(LEFT(REVERSE(FilePath), CHARINDEX('\', REVERSE(FilePath)) - 1));

3. 循环导入每个CSV文件到目标表 #

用游标遍历每个文件，通过OPENROWSET(BULK...)导入数据，同时把文件名插入到SourceFileName列：

DECLARE @CurrentFilePath NVARCHAR(255);
DECLARE @CurrentFileName NVARCHAR(100);
DECLARE @ImportSQL NVARCHAR(1000);

-- 声明游标遍历所有CSV文件
DECLARE FileCursor CURSOR FOR
SELECT FilePath, FileName FROM #AllCSVFiles;

OPEN FileCursor;
FETCH NEXT FROM FileCursor INTO @CurrentFilePath, @CurrentFileName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接导入SQL，根据你的CSV格式调整参数
    SET @ImportSQL = N'
        INSERT INTO dbo.TargetTable (Col1, Col2, Col3, SourceFileName)
        SELECT Col1, Col2, Col3, ''' + @CurrentFileName + ''' AS SourceFileName
        FROM OPENROWSET(
            BULK ''' + REPLACE(@CurrentFilePath, '\', '\\') + ''',
            FIRSTROW = 2, -- 如果CSV有表头，从第2行开始读取
            FIELDTERMINATOR = '','',
            ROWTERMINATOR = ''\n'',
            CODEPAGE = ''65001'' -- 如果是UTF-8编码的CSV，加上这个参数
        ) AS CSVData;
    ';

    -- 执行导入语句
    EXEC sp_executesql @ImportSQL;

    FETCH NEXT FROM FileCursor INTO @CurrentFilePath, @CurrentFileName;
END

CLOSE FileCursor;
DEALLOCATE FileCursor;

关键注意事项 #

• 权限问题：SQL Server的服务账户（比如NT SERVICE\MSSQLSERVER）必须拥有C:\Data\文件夹的读取权限，否则无法访问CSV文件。
• CSV格式适配：如果你的CSV有带引号的字段、自定义分隔符，要调整OPENROWSET里的参数；复杂格式可以创建SQL Server格式文件来匹配。
• xp_cmdShell启用：如果这个功能没开，先执行以下语句启用：

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'xp_cmdshell', 1;
  RECONFIGURE;
  

内容的提问来源于stack exchange，提问作者Praveen