使用查询或存储过程将文本文件导入SQL Server表并动态读取文件名

一、存储过程动态读取待导入文件名 #

无需每次手动修改文件名参数，可通过SQL Server内置功能遍历指定文件夹下的文本文件，动态拼接导入语句：

1. 首先按需开启xp_cmdshell权限（仅首次配置需要，若安全要求高可跳过此方式改用外部脚本传文件名）

-- 开启xp_cmdshell配置
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. 编写动态导入逻辑

CREATE PROCEDURE usp_ImportTxtToSql
AS
BEGIN
    -- 1. 临时表存储读取到的文件名
    CREATE TABLE #FileList (FileName VARCHAR(255));
    -- 读取指定文件夹下所有txt文件，/b参数仅返回文件名，/a-d排除子文件夹
    INSERT INTO #FileList 
    EXEC xp_cmdshell 'dir "C:\指定导入文件夹路径\*.txt" /b /a-d';

    -- 2. 筛选待导入的文件，可按前缀、生成时间等规则自定义筛选逻辑
    DECLARE @FileName VARCHAR(255), @ImportSql NVARCHAR(MAX), @BakCmd VARCHAR(1000);
    DECLARE file_cur CURSOR FOR 
    SELECT FileName FROM #FileList 
    WHERE FileName IS NOT NULL 
    -- 可自定义筛选规则，比如匹配当日日期前缀：FileName LIKE CONVERT(VARCHAR(8),GETDATE(),112)+'%.txt'
    ORDER BY FileName;

    OPEN file_cur;
    FETCH NEXT FROM file_cur INTO @FileName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 3. 动态拼接BULK INSERT语句
        SET @ImportSql = N'
        BULK INSERT 你的目标表名
        FROM ''C:\指定导入文件夹路径\' + @FileName + '''
        WITH (
            FIELDTERMINATOR = ''|'', -- 替换为实际的字段分隔符
            ROWTERMINATOR = ''\n'', -- 替换为实际的行分隔符
            FIRSTROW = 2 -- 若文件无表头则改为1
        )';
        EXEC sp_executesql @ImportSql;

        -- 4. 导入完成后将文件移动到备份文件夹，避免重复导入
        SET @BakCmd = 'move "C:\指定导入文件夹路径\' + @FileName + '" "C:\导入完成备份文件夹路径\"';
        EXEC xp_cmdshell @BakCmd, NO_OUTPUT;

        FETCH NEXT FROM file_cur INTO @FileName;
    END

    CLOSE file_cur;
    DEALLOCATE file_cur;
    DROP TABLE #FileList;
END

若不希望开启xp_cmdshell，可通过PowerShell、Python等外部脚本遍历文件夹获取文件名，再作为参数传入存储过程执行导入。

二、文件存入后自动触发导入 #

可根据你的业务时效要求选择以下任意一种方案：

• 方案1：SQL Server代理作业定时调度
  直接在SQL Server中新建代理作业，设置执行频率（比如每分钟执行一次），执行内容为调用上面写好的usp_ImportTxtToSql存储过程即可，无需额外开发，配置简单。
• 方案2：Windows任务计划程序
  写一段简单的PowerShell脚本调用存储过程，在Windows任务计划程序中设置定时执行该脚本，适合没有SQL Server代理权限的场景。
• 方案3：实时文件监控触发
  用C#/PowerShell的FileSystemWatcher类监控指定文件夹的文件创建事件，检测到新的txt文件写入完成后立刻调用存储过程执行导入，时效最高，适合对导入实时性要求高的场景。

注意事项 #

• 确保SQL Server服务运行账号对导入文件夹、备份文件夹有读写权限，避免出现权限不足导致的导入失败
• 导入前可加文件占用校验逻辑，避免文件还在写入过程中就被导入导致数据不全
• 建议新增导入日志表，记录每次导入的文件名、导入时间、导入行数、执行状态等信息，方便后续问题排查

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