无需SSIS 用OPENROWSET与bcp批量导入指定文件夹全部CSV文件

前置准备 #

• 开启SQL Server的xp_cmdshell功能（用于遍历目录下的文件）
• 确保SQL Server服务运行账户对X:\project\Input\input\路径有读写权限
• 提前创建最终存储导入数据的表，示例表名为csv_import_result，除业务所需ID字段外，可额外加文件名、导入时间字段便于溯源

实现步骤 #

1. 开启xp_cmdshell配置 #

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

2. 遍历目录获取所有CSV文件列表 #

-- 创建临时表存储文件名
CREATE TABLE #csv_files (file_name VARCHAR(500));
INSERT INTO #csv_files(file_name)
EXEC xp_cmdshell 'dir /b "X:\project\Input\input\*.csv"';
-- 过滤空值和异常行
DELETE FROM #csv_files WHERE file_name IS NULL OR file_name NOT LIKE '%.csv';
GO

3. 循环执行动态SQL批量导入 #

建议新增导入日志表，避免重复导入历史文件：

-- 首次执行创建导入记录表，后续无需重复执行
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'imported_file_log')
CREATE TABLE imported_file_log(
    file_name VARCHAR(500) PRIMARY KEY,
    import_time DATETIME DEFAULT GETDATE()
);
GO

循环导入逻辑：

DECLARE @file_name VARCHAR(500), @sql NVARCHAR(MAX);
DECLARE file_cursor CURSOR FOR
SELECT file_name FROM #csv_files 
WHERE file_name NOT IN (SELECT file_name FROM imported_file_log); -- 跳过已导入文件

OPEN file_cursor;
FETCH NEXT FROM file_cursor INTO @file_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接动态SQL，替换原固定文件名
    SET @sql = N'
    INSERT INTO csv_import_result(ID)
    select 
    case
    when charindex('';'',(substring(a.text,charindex('';'',a.text,1)+1,99))) = 0
    then ltrim(rtrim(substring(a.text,charindex('';'',a.text,1)+1,99)))
    else ltrim(rtrim(substring(
           a.text, 
           charindex('';'',a.text,1) + 1,
           charindex('';'', substring(a.text, charindex('';'', a.text, 1) + 1,99)) - 1 
            ) ) )
    end as ID
    from openrowset(bulk ''X:\project\Input\input\' + @file_name + ''',
    formatfile = ''X:\project\Input\input\formatfile.txt'',firstrow=2, format=''csv''
    ) as a;
    ';
    -- 执行导入
    EXEC sp_executesql @sql;
    -- 记录已导入文件
    INSERT INTO imported_file_log(file_name) VALUES(@file_name);

    FETCH NEXT FROM file_cursor INTO @file_name;
END

CLOSE file_cursor;
DEALLOCATE file_cursor;
-- 清理临时表
DROP TABLE #csv_files;
GO

可选优化 #

• 可将上述逻辑封装为存储过程，搭配SQL Server代理作业定时触发，适配文件夹频繁更新的场景
• 可添加异常捕获逻辑，避免单个文件格式错误导致整个批量导入任务终止

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