SQLServer数据库中的数据转存到Oracle数据库中

方法1:

最直接办法当然是database link

在你的local服务器上,建立一个database link (假设远程服务器的连接字是remote):

CREATE DATABASE LINK remotedblink CONNECT TO remoteuser IDENTIFIED BY remotepwd USING ‘remote’;

然后:

create table datatbl select * from datatbl@remotedblink

方法2:

需求:实现远程sqlserver数据库转存到oracle中,同时实现每天的凌晨两点转一次,一次转三个月内的数据,oracle中存在时就更新,不存在时就插入,因为数据有点多,一天数据就有五百条左右,插入时性能得调整。

难点:

1、两个数据库的连接(比较简单,但是细节很重要)

2、性能好点的插入更新sql

3、怎么实现每天定时更新

4、一次性同步数据较多,会使得系统直接卡死(我那破本本,重启了四五次)

SQLServer数据库中的数据转存到Oracle数据库中

解决方案:

1、对于oracle的连接其实很简单,因为oracle是本地的所以连接很easy,连接字符串搞定就行

</pre><pre name="code" class="csharp">//oracle连接字符串
string oracleConnectStr = "Data source=数据库名;user id=登录名;password=密码";
//例如:string oracleConnectStr = "Data source=orcl;user id=liems3;password=liems3";

2、对于sqlserver的连接有点坑爹,看是很简单,但是操作的时候各种坑爹,因为sqlserver版本太多,我自己测试用的2012,实施用的是2000,产生了代沟,怎么都连不上。

//sqlserver连接字符串
string sqlserverConnectStr = "Data Source=远程IP;Initial Catalog=数据库;User Id=登录名;Password=密码";
//string sqlserverConnectStr = "Data Source=168.168.10.77;Initial Catalog=jamie;User Id=sa;Password=jamie";

连接的时候会出现下面的异常:

异常 System.Data.sqlClient.sqlException (0x80131904): 在与 sql Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 sql Server 已配置为允许远程连接。 (provider: Named Pipes Provider,error: 40 – 无法打开到 sql Server 的连接)

之后就会使劲的去百度异常问题,但是有的时候可能都不是这些问题,就拿sqlserver 2000的来说吧,2000有个神马sqlserver注册组,注册组下有数据库,用查询分析器连接的时候127.0.0.1都连不进去,需要在后面加注册组中的完整地址像下面这样

string sqlserverConnectStr = "Data Source=127.0.0.1\\ERP;Initial Catalog=jamie;User Id=sa;Password=jamie";

 3、性能好点的插入语句这里介绍merge into 根据条件插入或更新,语句简单性能好,至于详细各位自己勾搭度娘去,merge into 能很好的同步数据库中两张表的数据,对于不同数据库的数据同步,我开始也是纠结了好久,之后提出两种方案,都是可行的,一种是建一张临时表将sqlserver中查到的数据插入进去之后再更新,这种是可行的,一个同事就是用的这种,一种是直接用sql将查到的数据拼成一张表(这里不知道如何表达,其实我也是第一次见到这么神奇的东东)

sqlStr=@"select 123 as dualp_id,'jamie' as dual_name,18 as dual_age from dual
union all
select 312 as dualp_id,'tom' as dual_name,19 as dual_age from dual
union all
select 321 as dualp_id,'jane' as dual_name,20 as dual_age from dual"

这样就形成了一张表,一张比临时表还临时的表。不要问我中间的原因是什么,我也不知道,大神叫我这么做的。之后再上merge into语句

sqlo = string.Format(@"merge into cement_in indata using ({0}) temp on(indata.FInterID=temp.FInterID and indata.FEntryID=temp.FEntryID)
when matched then
update set indata.FDate = temp.FDate,indata.FQty = temp.FQty,indata.fprice = temp.fprice,indata.famount = temp.famount,indata.funitid = temp.funitid,indata.FTranType =temp.FTranType,indata.FSupplyID = temp.FSupplyID,indata.FItemID = temp.FItemID,indata.fnumber = temp.fnumber,indata.itemname =temp.itemname,indata.fmodel =temp.fmodel,indata.supplyname =temp.supplyname,indata.unitname =temp.unitname,indata.typename =temp.typename
when not matched then 
insert 
(FInterID,FEntryID,FDate,FQty,fprice,famount,funitid,FTranType,FSupplyID,FItemID,fnumber,itemname,fmodel,supplyname,unitname,typename)
values
(temp.FInterID,temp.FEntryID,temp.FDate,temp.FQty,temp.fprice,temp.famount,temp.funitid,temp.FTranType,temp.FSupplyID,temp.FItemID,temp.fnumber,temp.itemname,temp.fmodel,temp.supplyname,temp.unitname,temp.typename)",sqlStr);//	组成的那个超临时表的sql语句	

这样一个OK的merge into sql 就出来了,sql好长呀,其实这是很短的,因为数据只有三条,想想数据达到几百条几千条之后又有多少页了,反正我将整个sql复制到plsql之中就等了好久plsql才反应过来。

4、接着就是数据太多性能的问题了,一般同步300条数据的时候一秒就ok了,但是到了500以上就蛋疼了,特别是5000条的时候整个本本都game over,等了十几分钟最后只能关机重启。对于这种问题,解决方案是将数据分为300条一组数据存入到一个list,之后每300条同步一次,效果一下就很明显了,5000条数据70秒就搞定了。so easy ,大神再也不会看到我关机重启了。

5、至于每天定时运行,这个其实也很简单,我用的是一个叫jobAnt的工具,大神提供的,各种碉堡了,定时运行无压力。下面就上代码吧!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.sqlClient;
using System.Data.OracleClient;
 
namespace DataTransform
{
	public class TransForm
	{
		//总共处理数据条数
		private int sumData=0;  
		//开始时间
		private DateTime startTime;
		//结束时间
		private DateTime endTime;
		//是否是操作入库表
		private bool isIn;
		//一次更新条数
		private int updateNumber = 500;
		//oracle连接字符串
		private string oracleConnectStr = "Data source=orcl;user id=liems3;password=liems3";
		//sqlserver连接字符串
		private string sqlserverConnectStr = "Data Source=168.168.10.77;Initial Catalog=jamie;User Id=sa;Password=jamie";
		//入库表的sql
		string sqlReadIn = string.Format(@"select * from cement_in where FDate between DateAdd(Month,-3,getdate()-1) and DateAdd(Month,-2,getdate()) ;");
		//出库表的sql
		string sqlReadOut = string.Format(@"select * from cement_out where FDate between DateAdd(Month,getdate()) ;");
		List<String> sqlList = null;
 
 
		public void ReadFromsqlServer(bool isin)
		{
			this.isIn = isin;
			sqlConnection sqlCon = null;
			sqlCommand cmdsql = null;
			sqlDataReader dataReader = null;
			OracleConnection conn = null;
			OracleCommand cmdORA = null;
 
			try
			{
				sqlCon = new sqlConnection(sqlserverConnectStr);
				sqlCon.Open();
				conn = new OracleConnection(oracleConnectStr);
				conn.Open();
 
				string sqls = null;
				if (isIn)
				{
					sqls = sqlReadIn;
				}
				else
				{
					sqls = sqlReadOut;
				}
				cmdsql = new sqlCommand(sqls,sqlCon);
				dataReader = cmdsql.ExecuteReader();
				string sqlUion = "";
				int num = 1;
				sqlList = new List<string>();
				while (dataReader.Read())
				{
					string strTemp;
					if (isIn)
					{
						strTemp = string.Format(@"select 
													{0} as FInterID,{1} as FEntryID,to_date('{2}','yyyy-mm-dd hh24:mi:ss') as FDate,{3} as FQty,{4} as fprice,{5} as famount,{6} as funitid,{7} as FTranType,'{8}' as FSupplyID,{9} as FItemID,{10} as fnumber,'{11}' as itemname,'{12}' as fmodel,'{13}' as supplyname,'{14}' as unitname,'{15}' as typename from  dual",Convert.ToInt32(dataReader["FInterID"]),Convert.ToInt32(dataReader["FEntryID"]),Convert.ToString(dataReader["FDate"]),Convert.ToDouble(dataReader["FQty"]),Convert.ToDouble(dataReader["fprice"]),Convert.ToDouble(dataReader["famount"]),Convert.ToInt32(dataReader["funitid"]),Convert.ToInt32(dataReader["FTranType"]),Convert.ToString(dataReader["FSupplyID"]),Convert.ToInt32(dataReader["FItemID"]),Convert.ToDouble(dataReader["fnumber"]),Convert.ToString(dataReader["itemname"]),Convert.ToString(dataReader["fmodel"]),Convert.ToString(dataReader["supplyname"]),Convert.ToString(dataReader["unitname"]),Convert.ToString(dataReader["typename"])
							);
					}
					else
					{
						strTemp = string.Format(@"select 
													{0} as FInterID,{6} as saleprice,{7} as saleamount,'{8}' as funitid,{9} as FTranType,{10} as FSupplyID,'{11}' as FItemID,{12} as fnumber,'{13}' as itemname,'{14}' as supplyname,'{15}' as unitname,'{16}' as typename from  dual",Convert.ToInt32(dataReader["saleprice"]),Convert.ToInt32(dataReader["saleamount"]),Convert.ToInt32(dataReader["FSupplyID"]),Convert.ToString(dataReader["typename"]));
					}
					sqlUion += strTemp;
					if (num % updateNumber == 0)
					{
						sqlList.Add(sqlUion);
						sqlUion = "";
					}
					else
					{
						sqlUion += " union all ";
					}
					num++;
				}
				if (!"".Equals(sqlUion))
				{
					string sqlu = sqlUion.Substring(0,sqlUion.Length - 10);
					sqlList.Add(sqlu);
				}
				string sqlo = null;
 
				foreach (var item in sqlList)
				{
					if (isIn)
					{
						sqlo = string.Format(@"merge into cement_in indata using ({0}) temp on(indata.FInterID=temp.FInterID and indata.FEntryID=temp.FEntryID)
							when matched then
							update set indata.FDate = temp.FDate,indata.typename =temp.typename
							when not matched then 
							insert 
							(FInterID,typename)
							values
							(temp.FInterID,item);
					}
					else
					{
						sqlo = string.Format(@"merge into cement_out outdata using ({0}) temp on(outdata.FInterID=temp.FInterID and outdata.FEntryID=temp.FEntryID)
							when matched then
							update set outdata.FDate = temp.FDate,outdata.FQty = temp.FQty,outdata.fprice = temp.fprice,outdata.famount = temp.famount,outdata.funitid = temp.funitid,outdata.FTranType =temp.FTranType,outdata.FSupplyID = temp.FSupplyID,outdata.FItemID = temp.FItemID,outdata.fnumber = temp.fnumber,outdata.itemname =temp.itemname,outdata.saleprice =temp.saleprice,outdata.saleamount =temp.saleamount,outdata.supplyname =temp.supplyname,outdata.unitname =temp.unitname,outdata.typename =temp.typename
							when not matched then 
							insert 
							(FInterID,saleprice,saleamount,temp.saleprice,temp.saleamount,item);
					}
					cmdORA = new OracleCommand(sqlo,conn);
					var val = cmdORA.ExecuteNonQuery();
					if (val >= 1)
					{
						Console.WriteLine("insert or update count: " + val);
					}
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				cmdORA.Dispose();
				conn.Close();
				dataReader.Close();
				cmdsql.Dispose();
				sqlCon.Close();
			}
		}
	}
}
<a target=_blank href="http://download.csdn.net/detail/jiangyou4/8535829">点击打开链接</a>
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论