利用SQL腳本完成數(shù)據(jù)庫同步

roych

前段時間，需要處理數(shù)據(jù)庫同步。由于之前切換服務(wù)器（額，那個locky……請自行百度），然后重裝了系統(tǒng)，也不知道是因為權(quán)限問題還是端口的原因，反正后面試了好幾次，發(fā)布訂閱數(shù)據(jù)庫都出錯，于是只能退而求其次，打算用腳本來同步。

思路很簡單，大體是先把原先存在的舊數(shù)據(jù)刪除，再追加新數(shù)據(jù)。通過系統(tǒng)自帶的計劃任務(wù)，每天運(yùn)行作業(yè)，從而達(dá)到同步的目的。需要解決的幾個技術(shù)點分別是：

1、如何獲取遠(yuǎn)程數(shù)據(jù)庫�！�—鏈接服務(wù)器。大體是在管理工具/ODBC數(shù)據(jù)源上添加系統(tǒng)DSN，輸入用戶名，密碼，服務(wù)器名稱，數(shù)據(jù)庫名，大體就好了。最后在服務(wù)器屬性
2、要不要使用truncate。——如果存量數(shù)據(jù)較多（例如上千萬條記錄），建議不要使用truncate，而改用delete from where，畢竟插入1000萬條記錄，按SQL Server的性能，沒一個小時大概是搞不定的（Sybase問題不大。而MySQL嘛，大概還要更慢，除非你優(yōu)化得很好。）。
3、考慮到存量數(shù)據(jù)可能會更新，個人不建議使用insert into left join null方式來插入數(shù)據(jù)。
4、如何處理字段�！�—思路是有了，但總不能一個個字段手動敲進(jìn)去吧？select 字段1,字段2……因此，可以考慮使用select distinct name from syscolumns where id =OBJECT_ID(表名稱)來獲取表字段，然后拼接為字符串。拼接的方法有兩種，一個是用游標(biāo)，另一個是用遞歸查詢（從嚴(yán)格意義上說，這個其實算不上遞歸查詢，因為沒用CTE的特有表達(dá)式）。游標(biāo)嘛，我試過，所以推薦用后者。
確定這幾個問題之后，就可以寫腳本了：

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. --獲取前一天數(shù)據(jù)
    
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. --使用鏈接服務(wù)器，完成數(shù)據(jù)同步操作                       
    
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
    
35. 
    
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

復(fù)制代碼

5、接下來創(chuàng)建作業(yè)，設(shè)置好計劃任務(wù)屬性（不是控制面板的那個哦）就好了�；�本設(shè)置如下，并不復(fù)雜，所以這里就不全部貼圖了。

tmtony

強(qiáng)！坐個沙發(fā)！

leonshi

借鑒下，有時間試試

boon

厲害了我的哥，謝謝分享