sqlserver数据库转存到oracle

发布时间：2020-12-12 12:48:18 所属栏目：MsSql教程来源：网络整理

导读：需求：实现远程sqlserver数据库转存到oracle中，同时实现每天的凌晨两点转一次，一次转三个月内的数据，oracle中存在时就更新，不存在时就插入，因为数据有点多，一天数据就有五百条左右，插入时性能得调整。难点： 1、两个数据库的连接（比较简单，但是细

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

难点：
1、两个数据库的连接（比较简单，但是细节很重要）
2、性能好点的插入更新sql
3、怎么实现每天定时更新
4、一次性同步数据较多，会使得系统直接卡死（我那破本本，重启了四五次）

解决方案：
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.1ERP;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>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!