跨数据库合并临时表查询结果及文件名扩展名截断需求

完整合并脚本 #

-- 创建第一个临时表 #tmpVLT
IF OBJECT_ID('tempdb..#tmpVLT') IS NOT NULL
    DROP TABLE #tmpVLT

CREATE TABLE #tmpVLT(
    [OrigName] VARCHAR(250),
    [mailbox] VARCHAR(100),
    [action] VARCHAR(100),
    [transport] VARCHAR(50),
    [startDT] DATETIME,
    [endDT] DATETIME,
    [Direction] VARCHAR(100),
    [Status] VARCHAR(100),
    [origPath] VARCHAR(100),
    [filesize] VARCHAR(100),
    [transfertime] VARCHAR(100),
    [transferbytes] VARCHAR(100)
)

INSERT INTO #tmpVLT
SELECT 
    LEFT([OrigName], LEN([OrigName]) - 4) AS OrigName, -- 截断最后4位去除.txt/.csv扩展名
    [mailbox],
    [action],
    [transport],
    [startDT],
    [endDT],
    [Direction],
    [Status],
    [origPath],
    [filesize],
    [transfertime],
    [transferbytes]
FROM 
    [EDI].[dbo].[VLTransfers]
WHERE 
    origpath LIKE '//EDIS%/in/PROD' 
    AND direction = 'receive' 
    AND StartDT >= DATEADD(DAY, -7, GETDATE())

-- 创建第二个临时表 #tmpEDIS
IF OBJECT_ID('tempdb..#tmpEDIS') IS NOT NULL
    DROP TABLE #tmpEDIS 

CREATE TABLE #tmpEDIS(
    [OriginalName] VARCHAR(250),
    [FILECatalogedOn] DATETIME,
    [filetype] VARCHAR(50)
)

INSERT INTO #tmpEDIS
SELECT
    LEFT(OriginalName, LEN(OriginalName) - 4) AS OriginalName, -- 截断最后4位去除扩展名
    FileCatalogedOn,
    'rx' AS filetype
FROM EDIPlatform.dbo.RXInboundFileQueue
WHERE FILECatalogedOn >= DATEADD(DAY, -7, GETDATE())

UNION ALL

SELECT
    LEFT(OriginalName, LEN(OriginalName) - 4) AS OriginalName,
    FileCatalogedOn,
    'eligibility' AS filetype
FROM EDIPlatform.dbo.EligibilityInboundFileQueue
WHERE FILECatalogedOn >= DATEADD(DAY, -7, GETDATE())

UNION ALL

SELECT
    LEFT(OriginalName, LEN(OriginalName) - 4) AS OriginalName,
    FileCatalogedOn,
    'costfile' AS filetype
FROM EDIPlatform.dbo.CostFileInboundFileQueue
WHERE FILECatalogedOn >= DATEADD(DAY, -7, GETDATE())

-- 查询临时表用于数据对比
SELECT * FROM #tmpVLT ORDER BY OrigName DESC, StartDT DESC
SELECT * FROM #tmpEDIS ORDER BY OriginalName DESC, FILECatalogedOn DESC

关键修改说明 #

• 文件名处理：对两个表的文件名列使用LEFT(column_name, LEN(column_name)-4)函数，精准截断最后4个字符以去除.txt或.csv扩展名
• 脚本合并：将两个独立的临时表创建、插入逻辑整合为一个连贯脚本，方便一次性执行
• 性能优化：移除INSERT语句中的ORDER BY（临时表插入时排序不影响数据存储，后续查询阶段排序更高效）

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