sql merge 的原理和基本用法可参考 sqlserver merge用法
如果是同一服务器不同库表进行同步,按这篇文章直接创建个作业即可。那么问题来了,跨服务器如何能过代码实现?
场景:
客户服务器属于内网,客户提供了查询人员的接口,通过接口可拿到人员的集合,拿到集合再去与表去做同步。
思路:
由于 merge 进行的同步操作是表与表之间的同步,当前得到的只有人员集合,把查询接口得到的人员集合存至表中,然后再去做同步。
实现:
IDbConnection conn = new SqlConnection("Data Source=.;Initial Catalog=KBDB;uid=joe;pwd=joe-123;"); string createTabSql = "create table PeoCursor(ID nvarchar(50),Name nvarchar(50),UserName nvarchar(50),PassWord nvarchar(50),PeoTypeID nvarchar(50))"; conn.Execute(createTabSql); List<peoTableInfo> peoList = new List<peoTableInfo>(); peoTableInfo peoa = new peoTableInfo("peo101", "张三三", "zhang", "123456", "1"); peoTableInfo peob = new peoTableInfo("peo101", "李三三", "li", "123456", "1"); peoList.Add(peoa); peoList.Add(peob); conn.Execute("insert into PeoCursor values (@ID,@Name,@UserName,@PassWord,@PeoTypeID)", peoList); string tbsql = "merge [KBDB].[dbo].[kb_People] as a " + "using PeoCursor as b " + "on a.ID=b.ID " + "when MATCHED then " + "update set a.name = b.name,a.username = b.username,a.password = b.password,a.peotypeid = b.peotypeid " + "WHEN NOT MATCHED THEN " + "insert(id, name, username, password, peotypeid) values(b.id, b.name, b.username, b.password, b.peotypeid);"; conn.Execute(tbsql); conn.Execute("drop table PeoCursor"); List<peoTableInfo> pList = conn.Query<peoTableInfo>("select * from kb_People").ToList();
用到的实体peoTableInfo:
public class peoTableInfo { public peoTableInfo() { } public peoTableInfo(string ID,string Name,string UserName,string PassWord,string PeoTypeID) { this.ID = ID; this.Name = Name; this.UserName = UserName; this.PassWord = PassWord; this.PeoTypeID = PeoTypeID; } public string ID { get; set; } public string Name { get; set; } public string UserName { get; set; } public string PassWord { get; set; } public string PeoTypeID { get; set; } }
执行前:
执行后:
注意事项总结:
创建的库表不得与当前库任一表重名,记得加一下判断。
表结构必须一致。
用完后记得删除表,以免造成脏数据。
为什么不用临时表?----因为临时表创建后,使用时会面临找不到表的问题。坑太多,甚用。
定期同步如何实现? ----代码扔进控制台程序中。生成exe然后去做windows计划任务。