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

        我们知道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】的访问接口。关于链接服务器属性设置如下图所示：

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

 

 

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

 

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

 

 

 

/*添加[导入客户的数据库]存储过程by:Alex Yu,2012-11-26*/if object_id('usp_dgw_ExprotTrafficDataForYinTai') is not nulldrop proc usp_dgw_ExprotTrafficDataForYinTaigocreate proc usp_dgw_ExprotTrafficDataForYinTaiasbegindeclare @IP nvarchar(20)declare @ExportTime datetimedeclare @CurrentTime datetime set @CurrentTime=DateAdd(ss,-2,getdate()) --当前系统时间建两秒作为当前时间select @ExportTime=ExportTime from ExportForOracle_Tableselect @IP=IP from IP_tableif(@IP is null)beginreturn endelsebegin TRANSACTION--获取满足ExprotTime不为空，基础数据表中的DateTime大于ExprotTime且ChannelIndex不为空的数据作为基础填充到Oracle数据库中表PDCDATA的基础数据#temPDCDATAselect 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.ChannelIndexinto #temPDCDATAfrom Counter_Table CTinner join dbo.Channel C on CT.CameraNo=C.CameraNoinner join dbo.Device D on D.DeviceID=C.DeviceIDwhere 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 tpdcdatawhere 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) BEGINROLLBACK TRANSACTIONRETURNENDELSEBEGINCOMMIT TRANSACTION DROP Table #temPDCDATAEND ENDGO

第三步：作业任务部署或第三方平台部署该任务一定周期的执行。

  在上述过程中第一二不配置需谨慎，重在理解性的基础上进行操作。