Office中国论坛/Access中国论坛

标题: 利用SQL脚本完成数据库同步 [打印本页]
作者: roych    时间: 2018-3-6 06:32
标题: 利用SQL脚本完成数据库同步
前段时间，需要处理数据库同步。由于之前切换服务器（额，那个locky……请自行百度），然后重装了系统，也不知道是因为权限问题还是端口的原因，反正后面试了好几次，发布订阅数据库都出错，于是只能退而求其次，打算用脚本来同步。

思路很简单，大体是先把原先存在的旧数据删除，再追加新数据。通过系统自带的计划任务，每天运行作业，从而达到同步的目的。需要解决的几个技术点分别是：

1、如何获取远程数据库。——链接服务器。大体是在管理工具/ODBC数据源上添加系统DSN，输入用户名，密码，服务器名称，数据库名，大体就好了。最后在服务器属性
2、要不要使用truncate。——如果存量数据较多（例如上千万条记录），建议不要使用truncate，而改用delete from where，毕竟插入1000万条记录，按SQL Server的性能，没一个小时大概是搞不定的（Sybase问题不大。而MySQL嘛，大概还要更慢，除非你优化得很好。）。
3、考虑到存量数据可能会更新，个人不建议使用insert into left join null方式来插入数据。
4、如何处理字段。——思路是有了，但总不能一个个字段手动敲进去吧？select 字段1,字段2……因此，可以考虑使用select distinct name from syscolumns where id =OBJECT_ID(表名称)来获取表字段，然后拼接为字符串。拼接的方法有两种，一个是用游标，另一个是用递归查询（从严格意义上说，这个其实算不上递归查询，因为没用CTE的特有表达式）。游标嘛，我试过，所以推荐用后者。
确定这几个问题之后，就可以写脚本了：
  1. create proc A9_Sametime(@tblName varchar(50),@dateFieldName varchar(50),@joinFieldName varchar(50)) AS
  2. declare @colname varchar(1800),
  3. @sqlDelOld varchar(8000),
  4. @sqlIndertNew varchar(8000),
  5. @startDate date,
  6. --长日期
  7. @startDateTime datetime,
  8. @endDateTime datetime
  9. set @startDate=GETDATE()
  10. --获取前一天数据
  11. set @startDateTime=DATEADD(day,-1,@startDate)
  12. set @endDateTime=DATEADD(MILLISECOND,-3,convert(datetime,@startdate))
  13. set @colname=''
  14. --递归查询，获取所有字段
  15. select @colname=@colname+c.name+ ',' from (select distinct name from syscolumns where id =OBJECT_ID(@tblName) ) c
  16. set @colname=substring(@colname,1,len(@colname)-1)
  17. --使用临时表（逗你玩的，渣渣的临时表）
  18. /*
  19. set  @sql='select * into #temp'
  20.                         +@colname+' from openquery(A9Server,'+'''select '+@colname
  21.                         +' from [A9SERVER].[S60623].[dbo].['+@tblName+']'
  22.                         +' where '+@dateFieldName+' between '''''
  23.                         +convert(varchar(23),@startDateTime,21)
  24.                         +''''' and '''''+convert(varchar(23),@endDateTime,21)+''''';'') '
  25. */
  26. --使用链接服务器，完成数据同步操作                       
  27. set @sqlDelOld='delete [A9].[dbo].['+@tblName+'] from'+
  28.                                         ' openquery(A9Server,'+'''select '+@colname
  29.                                         +' from [A9SERVER].[S60623].[dbo].['+@tblName+']'
  30.                                         +' where '+@dateFieldName+' between '''''
  31.                                         +convert(varchar(23),@startDateTime,21)
  32.                                         +''''' and '''''+convert(varchar(23),@endDateTime,21)+''''';'') A '
  33.                                         +' left join [A9].[dbo].['+@tblName+'] b'
  34.                                         +' on a.'+@joinFieldName+'=b.'+@joinFieldName

  36. set @sqlIndertNew='insert into [A9].[dbo].['+@tblName+']('+@colname+')  select '
  37.                                 +@colname+' from openquery(A9Server,'+'''select '+@colname
  38.                                 +' from [A9SERVER].[S60623].[dbo].['+@tblName+']'
  39.                                 +' where '+@dateFieldName+' between '''''
  40.                                 +convert(varchar(23),@startDateTime,21)
  41.                                 +''''' and '''''+convert(varchar(23),@endDateTime,21)+''''';'') '
  42. begin
  43.         exec(@sqlDelOld)
  44.         exec(@sqlIndertNew)
  45. end
复制代码
5、接下来创建作业，设置好计划任务属性（不是控制面板的那个哦）就好了。基本设置如下，并不复杂，所以这里就不全部贴图了。
[attach]62457[/attach]
[attach]62456[/attach]

作者: tmtony    时间: 2018-3-6 07:05
强！坐个沙发！
作者: leonshi    时间: 2018-3-7 03:28
借鉴下，有时间试试
作者: boon    时间: 2019-2-21 19:56
厉害了我的哥，谢谢分享



欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3