利用SQL脚本完成数据库同步

roych · 发表于 2018-3-6 06:32:07

前段时间，需要处理数据库同步。由于之前切换服务器（额，那个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的特有表达式）。游标嘛，我试过，所以推荐用后者。
确定这几个问题之后，就可以写脚本了：

create proc A9_Sametime(@tblName varchar(50),@dateFieldName varchar(50),@joinFieldName varchar(50)) AS
declare @colname varchar(1800),
@sqlDelOld varchar(8000),
@sqlIndertNew varchar(8000),
@startDate date,
--长日期
@startDateTime datetime,
@endDateTime datetime
set @startDate=GETDATE()
--获取前一天数据
set @startDateTime=DATEADD(day,-1,@startDate)
set @endDateTime=DATEADD(MILLISECOND,-3,convert(datetime,@startdate))
set @colname=''
--递归查询，获取所有字段
select @colname=@colname+c.name+ ',' from (select distinct name from syscolumns where id =OBJECT_ID(@tblName) ) c
set @colname=substring(@colname,1,len(@colname)-1)
--使用临时表（逗你玩的，渣渣的临时表）
/*
set @sql='select * into #temp'
+@colname+' from openquery(A9Server,'+'''select '+@colname
+' from [A9SERVER].[S60623].[dbo].['+@tblName+']'
+' where '+@dateFieldName+' between '''''
+convert(varchar(23),@startDateTime,21)
+''''' and '''''+convert(varchar(23),@endDateTime,21)+''''';'') '
*/
--使用链接服务器，完成数据同步操作
set @sqlDelOld='delete [A9].[dbo].['+@tblName+'] from'+
' openquery(A9Server,'+'''select '+@colname
+' from [A9SERVER].[S60623].[dbo].['+@tblName+']'
+' where '+@dateFieldName+' between '''''
+convert(varchar(23),@startDateTime,21)
+''''' and '''''+convert(varchar(23),@endDateTime,21)+''''';'') A '
+' left join [A9].[dbo].['+@tblName+'] b'
+' on a.'+@joinFieldName+'=b.'+@joinFieldName
set @sqlIndertNew='insert into [A9].[dbo].['+@tblName+']('+@colname+') select '
+@colname+' from openquery(A9Server,'+'''select '+@colname
+' from [A9SERVER].[S60623].[dbo].['+@tblName+']'
+' where '+@dateFieldName+' between '''''
+convert(varchar(23),@startDateTime,21)
+''''' and '''''+convert(varchar(23),@endDateTime,21)+''''';'') '
begin
exec(@sqlDelOld)
exec(@sqlIndertNew)
end