SQL Server 数据导入Oracle数据库（脚本实现）

时间：2021-07-08 06:42:37


        我们知道SQL Server在2005以后提供了DTS导出数据功能，可以将SQL Server数据库端数据导入到SQL Server，MySQL,Excel，Access以及Oracle等我们常用的数据库。然而在SQL Server 2005 Express 2005版本上没有这个功能，也就不能完成SQL Server 端数据导入到Oracle数据库中。这时候我们有什么办法可以解决SQL Serve数据导入到Oracle数据库中的需求呢？我们很简单会想到写一个控制台应用程序，从SQL Server端数据库中获取数据后遍历导入到Oracle数据库中；还有一个方法是数据库端直接数据交互。

      这里我给大家讲述一下关于【数据库端直接交互】。数据库端直接交互分为三步：第一建立SQL Server跟Oracle的访问接口；第二写实现逻辑数据的从SQL Server数据库的获取并写入Oracle数据库端的存储过程；第三用SQL Server的作业任务来建立定时导入到Oracle数据库端的作业任务（或用第三方的工具，具备Windows自带的计划任务即可，由于鄙人在实现该方案时借用功能内部产品DataServer来建立作业任务调用第二步中所建立的存储过程）。

第一步：DBLink的建立：      

SQL Server数据库跟Oracle数据库端数据直接交互，我们知道可以通过OpenQuery(DBLink,Sql Query)当Sql Query是从Oracle端某具体表中拿数据时，就可以完成SQL 数据库中从Oracle数据库中获取数据。这里面有很重要的一个通道BDLink，微软为MS SQL Server数据跟Oracle数据交互提供了一个借口为【Microsoft OLE DB Provider for Oracle】的访问接口。关于链接服务器属性设置如下图所示：

                 SQL Server 数据导入Oracle数据库（脚本实现）/*
添加[导入客户的数据库]存储过程
另外需要设置其【安全属性】为其配置用户名和密码，以免该DBLink在链接通道的通讯过程中拥有通行证，如下图所示。

 SQL Server 数据导入Oracle数据库（脚本实现）

 

按上述过程操作完成后可通过SQL端或者Oracle的客户端进行测试BDLink建立是否可以通过。

 

第二步：实现逻辑数据的从SQL Server数据库的获取并写入Oracle数据库端的存储过程，必须保证所建立的BDLink跟SQL脚本中所用到的名称一致，不然是连接不同的，我们可以形象的认为所见的DBLink就是SQL Server跟Oracle来往的一个管道。如图如代码所示。

 

 SQL Server 数据导入Oracle数据库（脚本实现）

 

/*
添加[导入客户的数据库]存储过程
by:Alex Yu,2012-11-26
*/
if object_id('usp_dgw_ExprotTrafficDataForYinTai') is not null
drop proc usp_dgw_ExprotTrafficDataForYinTai
go
create proc usp_dgw_ExprotTrafficDataForYinTai
as
begin
declare @IP nvarchar(20)
declare @ExportTime datetime
declare @CurrentTime datetime 
set @CurrentTime=DateAdd(ss,-2,getdate()) --当前系统时间建两秒作为当前时间
select @ExportTime=ExportTime from ExportForOracle_Table
select @IP=IP from IP_table

if(@IP is null)
begin
return 
end
else
begin TRANSACTION

--获取满足ExprotTime不为空，基础数据表中的DateTime大于ExprotTime且ChannelIndex不为空的数据作为基础填充到Oracle数据库中表PDCDATA的基础数据#temPDCDATA
select D.IP,C.ChannelIndex ChannelID,CT.DateTime as STARTTIME,DATEADD(SS,-1,DATEADD(MI,5,CT.DateTime)) as ENDTIME
,CT.Up as ENTERDATA,CT.Down as LEAVEDATA--,CT.ModifyTime,C.ChannelIndex
into #temPDCDATA
from Counter_Table CT
inner join dbo.Channel C on CT.CameraNo=C.CameraNo
inner join dbo.Device D on D.DeviceID=C.DeviceID
where CT.ModifyTime is not null and CT.ModifyTime>=@ExportTime and CT.ModifyTime< @CurrentTime and C.ChannelIndex is not null

    --当导入的数据在Oracle表中不存在则Insert进来
Insert into DBLINK..SYSTEM.PDCDATA select 0, tpdcdata.* from #temPDCDATA tpdcdata
where not exists
(select * from DBLINK..SYSTEM.PDCDATA pdata where pdata.IP=tpdcdata.IP 
and pdata.Channel=tpdcdata.ChannelID and pdata.STARTTIME=tpdcdata.STARTTIME
)
--当导入的数据在Oracle表中存在则Update过来
UPDATE DBLINK..SYSTEM.PDCDATA
    SET ENTERDATA = (SELECT b.ENTERDATA FROM  #temPDCDATA b WHERE b.IP = a.IP and b.ChannelID=a.Channel and b.STARTTIME=a.STARTTIME),
    LEAVEDATA = (SELECT b.LEAVEDATA FROM  #temPDCDATA b WHERE  b.IP = a.IP and b.ChannelID=a.Channel and b.STARTTIME=a.STARTTIME)
    from DBLINK..SYSTEM.PDCDATA  a
    WHERE a.IP=(SELECT b.IP FROM #temPDCDATA b WHERE b.IP = a.IP) 
    and a.Channel=(SELECT b.ChannelID FROM #temPDCDATA b WHERE b.ChannelID = a.Channel) 
    and a.STARTTIME=(SELECT b.STARTTIME FROM #temPDCDATA b WHERE b.STARTTIME = a.STARTTIME) 

Update Counter_Table set ModifyTime=getdate() from Counter_Table --将基础数据表中的ExportTime均更行至当前时间

if (@@error<>0) 
BEGIN
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION 
DROP Table #temPDCDATA
END 
END
GO
第三步：作业任务部署或第三方平台部署该任务一定周期的执行。
  在上述过程中第一二不配置需谨慎，重在理解性的基础上进行操作。
标签：

相关文章