SQL Server数据实时同步至Oracle数据库中

1、使用SSMS中的链接服务器链接Oracle

请参考链接  SQLServer 客户端链接服务器到Oracle数据库 全攻略_sqlserver连接oracle数据库_简单的你我1314的博客-CSDN博客

以上链接中图形化界面创建或命令创建链接服务器任选其一即可

2、使用触发器（insert update delete）获取更改的数据然后同步至Oracle中（使用openquery查询）

//openquery语句 ORCL是链接服务器的名称 
INSERT openquery (ORCL,'SELECT "id","name","age" FROM system."person"') values(3,'本司',30)//
delete openquery  (ORCL,'SELECT "id","name","age" FROM system."person" where "id"=1')

select * from openquery  (ORCL,'SELECT "id","name","age" FROM system."person" ')

　　

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
if exists(select * from sys.triggers where name = 'insertTriger')//如果触发器存在 删掉触发器
drop trigger insertTriger
go
CREATE TRIGGER insertTriger //创建触发器
   ON  demo.dbo.person 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @id as int;
    declare @name as nvarchar(50);
    declare @age as int;
    select @id= inserted.id from inserted; //inserted为SQL Server的逻辑表 还有deleted表
    select @name= inserted.name from inserted;
    select @age= inserted.age from inserted;
    -- Insert statements for trigger here
    INSERT openquery (ORCL,'SELECT "id","name","age" FROM system."person"') values(@id,@name,@age) //获取插入的数据后 同步插入至Oracle
END
GO