如何按文件名日期筛选近6个月XML文件加载或重载至SQL？

核心思路 #

取消原代码中的Truncate操作以保留所有文件历史记录，通过解析文件名中的日期区分新增文件和需重载的近6个月文件，分别进行插入或更新操作，超过6个月的文件仅保留记录不处理。

步骤实现 #

1. 调整文件列表获取方式 #

用临时表存储当前文件夹的文件列表，避免清空历史记录：

-- 创建临时表存xp_dirtree结果
CREATE TABLE #files (
    id INT IDENTITY(1,1),
    Subdirectory NVARCHAR(255),
    depth INT,
    [FILE] INT
)

INSERT INTO #files EXEC master.sys.xp_dirtree 'E:\123\Load_Test', 1, 1;

2. 解析文件名中的日期 #

从示例格式123_01032023_456.xml中提取ddMMyyyy格式的日期并转换为DATE类型：

SELECT 
    Subdirectory AS FileName,
    -- 提取第2个下划线后8位字符，转为日期
    CONVERT(DATE, SUBSTRING(Subdirectory, CHARINDEX('_', Subdirectory, CHARINDEX('_', Subdirectory)+1)+1, 8), 103) AS FileDate
INTO #CurrentFiles
FROM #files
WHERE [FILE] = 1

3. 分情况处理文件 #

新增文件（未记录在PG_FileList中的文件）

直接插入记录并加载XML：

INSERT INTO [1].[PG_FileList]
    ([FilePath], [DBFilePath], [FileName], [DateLoaded], [FileDate]) -- 建议新增FileDate字段存解析后的日期
SELECT 
    '\\1\Load_Test' AS FilePath,
    'E:\1\PG_Load_Test' AS DBFilePath,
    cf.FileName,
    GETDATE() AS DateLoaded,
    cf.FileDate
FROM #CurrentFiles cf
LEFT JOIN [1].[PG_FileList] fl ON cf.FileName = fl.FileName
WHERE fl.FileName IS NULL

需重载的近6个月文件

更新加载时间后重新加载XML：

-- 更新加载时间标记
UPDATE fl
SET fl.DateLoaded = GETDATE()
FROM [1].[PG_FileList] fl
JOIN #CurrentFiles cf ON fl.FileName = cf.FileName
WHERE cf.FileDate >= DATEADD(MONTH, -6, GETDATE())

-- 此处执行XML重载逻辑，例如调用自定义存储过程：
-- EXEC dbo.ReloadXMLData @FileName = cf.FileName

超过6个月的文件

保留PG_FileList中的现有记录，无需任何操作。

完整代码 #

-- 创建临时表存储当前文件夹文件
CREATE TABLE #files (
    id INT IDENTITY(1,1),
    Subdirectory NVARCHAR(255),
    depth INT,
    [FILE] INT
)

INSERT INTO #files EXEC master.sys.xp_dirtree 'E:\123\Load_Test', 1, 1;

-- 解析文件名日期生成当前文件列表
SELECT 
    Subdirectory AS FileName,
    CONVERT(DATE, SUBSTRING(Subdirectory, CHARINDEX('_', Subdirectory, CHARINDEX('_', Subdirectory)+1)+1, 8), 103) AS FileDate
INTO #CurrentFiles
FROM #files
WHERE [FILE] = 1

-- 处理新增文件
INSERT INTO [1].[PG_FileList]
    ([FilePath], [DBFilePath], [FileName], [DateLoaded], [FileDate])
SELECT 
    '\\1\Load_Test' AS FilePath,
    'E:\1\PG_Load_Test' AS DBFilePath,
    cf.FileName,
    GETDATE() AS DateLoaded,
    cf.FileDate
FROM #CurrentFiles cf
LEFT JOIN [1].[PG_FileList] fl ON cf.FileName = fl.FileName
WHERE fl.FileName IS NULL

-- 处理近6个月需重载的文件
UPDATE fl
SET fl.DateLoaded = GETDATE()
FROM [1].[PG_FileList] fl
JOIN #CurrentFiles cf ON fl.FileName = cf.FileName
WHERE cf.FileDate >= DATEADD(MONTH, -6, GETDATE())

-- 清理临时表
DROP TABLE #files
DROP TABLE #CurrentFiles

额外建议 #

• 在PG_FileList中新增FileDate字段（DATE类型），避免每次重复解析文件名，提升运行效率。
• 若需精准判断文件是否更新，可结合xp_getfiledetails获取文件修改时间，与历史记录对比后决定是否重载。

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