比较两个表,一个是mysql db,一个是MSSQL db
本文关键字:一个 db MSSQL mysql 两个 比较 | 更新日期: 2023-09-27 18:19:02
我正在c#中执行以下语句。有两个表Orders,Data。订单是一个mysql数据库。数据为MSSQL数据库
insert into orders (orders_id, customers_id, customers_cid, customers_vat_id, customers_name, customers_email_address)
select
o.*
from
Test.dbo.orders o
where
not exists (
select 1
from
CobraDemoData.dbo.Data a
where
a.email0 = o.customers_email_address
)
我已经为两个数据库创建了连接字符串
MySqlConnection sqlConn = new MySqlConnection("server=localhost;User Id=root;Password = 123456;Persist Security Info=True;database=xtcommerce";)
SqlConnection con1 = new SqlConnection(@"Data Source=SUBASH-LAPTOP'COBRA;Initial Catalog=CobraDemoData;Integrated Security=True");
你能告诉我下一步该怎么做吗?任何帮助都将是非常感激的。谢谢,Subash…
打开两个读取器,用相同的查询查询两个表,按相同的键排序,逐行比较,类似这样,(假设有两个连接- mssql和mysql:
)var sc1 = mssql.CreateCommand();
var sc2 = mysql.CreateCommand();
sc1.CommandText = sc2.CommandText = "select id, name, email, phone from yourtable ORDER BY Id";
sc1.CommandTimeout = sc2.CommandTimeout = 0;
using (var r1 = sc1.ExecuteReader())
{
using (var r2 = sc2.ExecuteReader())
{
while (true)
{
bool read1 = r1.Read();
if (read1 ^ r2.Read())
throw new Exception("Doesn't match!");
if(!read1) {Console.WriteLine("MATCH!!!"); break;}
for (int i = 0; i < r1.FieldCount; i++)
{
if (r1.IsDBNull(i) ^ r2.IsDBNull(i))
throw new Exception("Doesn't match!");
if (string.Compare(r1[i].ToString(), r2[i].ToString(), StringComparison.InvariantCultureIgnoreCase) != 0)
throw new Exception("Doesn't match!");
}
}
}
}
然后,您可以基于此示例实现更新逻辑。必须有一些唯一的字段,用于标识两个表中的行