SQL Server无表头制表符文件批量导入及7天数据存储方案问询

现有BULK INSERT方案的效率分析 #

• BULK INSERT本身是SQL Server中高效的批量导入工具，它直接读取文件数据写入数据库表，跳过了常规INSERT操作的大量日志和约束检查环节，比逐行插入效率高得多。
• 但你当前的写法存在明显问题：使用DBA_NG_INBOUND_ELIG_*.txt通配符会一次性导入所有匹配的文件，不管文件是否是最近7天的。如果文件夹里堆积了大量历史文件，这会额外占用IO和数据库资源，既浪费性能，也不符合你只保留最近7天数据的需求。

实现仅导入最近7天文件的存储过程方法 #

要实现只导入最近7天的文件，核心是先筛选出符合时间要求的目标文件，再逐个执行BULK INSERT。以下是可直接使用的存储过程代码：

CREATE PROCEDURE ImportRecentEligibilityFiles
AS
BEGIN
    SET NOCOUNT ON;

    -- 临时表存储文件列表和创建时间
    CREATE TABLE #FileList (
        FileName VARCHAR(255),
        CreateDate VARCHAR(50) -- 先存字符串，后续转datetime
    )

    -- 注意：如果xp_cmdshell未启用，先执行以下注释的语句开启（需管理员权限）
    -- EXEC sp_configure 'show advanced options', 1;
    -- RECONFIGURE;
    -- EXEC sp_configure 'xp_cmdshell', 1;
    -- RECONFIGURE;

    -- 获取指定路径下符合命名规则的文件及创建时间（/TC表示按创建时间排序，/B只返回文件名和日期）
    INSERT INTO #FileList (FileName, CreateDate)
    EXEC xp_cmdshell 'dir "\\DBAediarchive\shared\edi\ediarchive\Inbound\TPV\Eligibility\DBA_NG_INBOUND_ELIG_*.txt" /TC /B'

    -- 清理无效行（xp_cmdshell可能返回空行或错误信息）
    DELETE FROM #FileList 
    WHERE FileName IS NULL 
       OR CreateDate IS NULL 
       OR ISDATE(CreateDate) = 0

    -- 转换创建时间为datetime类型（格式根据系统区域调整，这里适配MM/DD/YYYY HH:MM格式）
    UPDATE #FileList
    SET CreateDate = CONVERT(VARCHAR(20), CONVERT(DATETIME, CreateDate, 101), 120)

    -- 准备游标遍历最近7天的文件
    DECLARE @FileCursor CURSOR;
    DECLARE @FullFilePath VARCHAR(500);

    SET @FileCursor = CURSOR FOR
        SELECT '\\DBAediarchive\shared\edi\ediarchive\Inbound\TPV\Eligibility\' + FileName
        FROM #FileList
        WHERE CONVERT(DATETIME, CreateDate) >= DATEADD(DAY, -7, GETDATE())

    -- 重建临时表（确保每次执行都是空表）
    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
        DROP TABLE #TempTable;

    CREATE TABLE #TempTable (
        Column1 VARCHAR(50),
        Column2 VARCHAR(50),
        Column3 VARCHAR(50),
        Column4 VARCHAR(50),
        Column5 VARCHAR(50),
        Column6 VARCHAR(50),
        Column7 VARCHAR(50),
        Column8 VARCHAR(50),
        Column9 VARCHAR(50),
        Column10 VARCHAR(50)
    )

    -- 循环导入每个符合条件的文件
    OPEN @FileCursor
    FETCH NEXT FROM @FileCursor INTO @FullFilePath

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接动态SQL，注意转义单引号
        DECLARE @BulkSql NVARCHAR(MAX) = N'
        BULK INSERT #TempTable
        FROM ''' + REPLACE(@FullFilePath, '''', '''''') + '''
        WITH (
            FIELDTERMINATOR = ''\t'',
            ROWTERMINATOR = ''\n'',
            FIRSTROW = 1,
            TABLOCK -- 加表锁提升批量插入效率
        );'

        EXEC sp_executesql @BulkSql

        FETCH NEXT FROM @FileCursor INTO @FullFilePath
    END

    CLOSE @FileCursor
    DEALLOCATE @FileCursor

    -- 可选：将临时表数据同步到正式表（如果需要替换旧数据，先清空再插入）
    -- TRUNCATE TABLE YourOfficialEligibilityTable;
    -- INSERT INTO YourOfficialEligibilityTable SELECT * FROM #TempTable;

    -- 清理临时表
    DROP TABLE #FileList
END
GO

关键注意事项 #

• 权限配置：SQL Server的服务账号必须拥有共享文件夹\\DBAediarchive\shared\edi\ediarchive\Inbound\TPV\Eligibility的读取权限，否则无法读取文件和执行BULK INSERT。
• xp_cmdshell启用：如果你的环境禁用了xp_cmdshell，需要管理员执行注释中的语句开启，或者改用CLR存储过程获取文件列表（但xp_cmdshell是最简便的方式）。
• 日期格式适配：dir /TC返回的日期格式依赖系统区域设置，如果你的服务器是中文环境，可能需要将CONVERT(DATETIME, CreateDate, 101)中的101改为111（对应YYYY/MM/DD格式）。
• 性能优化：添加TABLOCK选项可以让SQL Server获取表级锁，减少锁竞争，大幅提升批量插入的速度，适合大文件导入场景。

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