如何用OPENQUERY从Oracle链接服务器拉取增量数据到SQL Server

实现逻辑 #

先获取作业上次成功运行的时间，将时间参数传入Oracle侧的查询做过滤，仅返回新增或变更的数据，避免全量拉取浪费资源。

完整可运行代码 #

-- 1. 定义变量存储作业上次成功运行时间
DECLARE @last_run_time DATETIME
DECLARE @job_id binary(16)
DECLARE @sql NVARCHAR(MAX)

-- 2. 获取目标作业ID
SELECT @job_id = job_id FROM msdb.dbo.sysjobs WHERE name = N'Job-ABC'

-- 3. 查询作业上次成功运行时间，无记录则兜底为1900-01-01（首次运行拉全量）
SELECT TOP 1
    @last_run_time = CONVERT(DATETIME, RTRIM(run_date))
    + ((run_time / 10000 * 3600) 
    + ((run_time % 10000) / 100 * 60) 
    + (run_time % 10000) % 100) / (86399.9964)
FROM msdb..sysjobhistory sjh
WHERE
    sjh.step_id = 0 
    AND sjh.run_status = 1 
    AND sjh.job_id = @job_id
ORDER BY run_datetime DESC

SET @last_run_time = ISNULL(@last_run_time, '1900-01-01')

-- 4. 构造动态SQL，传递时间参数到Oracle侧过滤
SET @sql = N'
SELECT ColumnA, ColumnB, ColumnC, LastModifiedDate, CreatedDate  
FROM OPENQUERY(XYX, ''
    SELECT ColumnA, ColumnB, ColumnC, LastModifiedDate, Createddate 
    FROM TableA
    WHERE CreatedDate > to_date(''''' + CONVERT(VARCHAR(19), @last_run_time, 120) + ''''', ''''yyyy-mm-dd hh24:mi:ss'''')
        OR LastModifiedDate > to_date(''''' + CONVERT(VARCHAR(19), @last_run_time, 120) + ''''', ''''yyyy-mm-dd hh24:mi:ss'''')
'')
'

-- 5. 执行查询，结果可直接作为MERGE的源表使用
EXEC sp_executesql @sql

注意事项 #

• 日期格式兼容：代码中用CONVERT(...,120)将SQL Server的datetime转为通用的yyyy-mm-dd hh24:mi:ss格式，再用Oracle的to_date指定格式解析，避免不同环境默认日期格式不一致导致报错。
• 索引优化：建议在Oracle的TableA的CreatedDate和LastModifiedDate字段上建立联合索引，进一步提升Oracle侧的查询效率。
• 时区适配：如果Oracle和SQL Server部署时区不一致，需要先对@last_run_time做时区转换后再传入Oracle，避免时间差导致漏拉数据。

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