MsSQL跨数据库同步数据

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

ALTER  PROC [dbo].[P_Utils_SynchronousMssqlData]                 
(
 @srv_lnk NVARCHAR(200),
 @SourceTable NVARCHAR(300),                 
 @TargetTable NVARCHAR(300),
 @Source_Columns NVARCHAR(300),
 @Target_Columns NVARCHAR(300),             
 @Source_TMColumn NVARCHAR(300),
 @Target_TMColumn NVARCHAR(300),             
 @SQLWhere NVARCHAR(300)                      
)                 
/**                 
功能：SQLServer跨数据库数据同步程序                 
参数：@srv_lnk NVARCHAR(50) 链接服务器                 
参数：@SourceTable NVARCHAR(50) 同步数据的数据源
参数：@TargetTable NVARCHAR(50) 数据接收Table
参数：@Source_Columns NVARCHAR(100) 数据源待同步的字段(默认为*)
参数：@Target_Columns NVARCHAR(100) 本地待同步的字段(默认为所有)           
参数：@Source_TMColumn NVARCHAR(20) 数据源表时间字段
参数：@Target_TMColumn NVARCHAR(20) 接收到数据表时间字段            
参数：@SQLWhere NVARCHAR(100) 查询条件              
作者：贺翔                 
版本：2011-07-26                
范例：                 
EXEC P_Utils_SynchronousMssqlData @srv_lnk,@SourceTable,@TargetTable,
  @Source_Columns,@Target_Columns,@Source_TMColumn,@Target_TMColumn,@SQLWhere;

EXEC P_Utils_SynchronousMssqlData 'srv_lnk_yurun','yurun.dbo.rainview','ST_PPTN_R',
  'STCD,TM,R5','*','TM','TM','WHERE DATEPART(ss,TM)=0 ';--同步皎口雨情信息

EXEC P_Utils_SynchronousMssqlData 'local','宁波水情数据库.dbo.逐日雨情表','ST_PPTN_R',
'CODE5,日期,雨量','STCD,TM,DYP','日期','TM','WHERE CODE5 = 01011'
*/                 
AS                 
BEGIN
SET NOCOUNT ON               
  DECLARE                 
  @HisDB_maxTM DATETIME,/*历史库(本地库)History Database最新数据时间*/                 
  @SDB_maxTM DATETIME,/*数据源(远程库)Source Database最新数据时间*/
  @WHERE NVARCHAR(100),/*WHERE 拼接SQL*/                 
  @SQL NVARCHAR(3000);/*SQL语句*/
  /*初始化数据*/
  SET @TargetTable = (CASE WHEN (@TargetTable IS NULL OR @TargetTable = '') THEN @SourceTable ELSE @TargetTable END);
  SET @Source_Columns = (CASE WHEN (@Source_Columns IS NULL OR @Source_Columns = '') THEN '*' ELSE @Source_Columns END);
  SET @Target_Columns = (CASE WHEN (@Target_Columns IS NULL OR @Target_Columns = '' OR @Target_Columns = '*') THEN '' ELSE '('+@Target_Columns+')' END);
  SET @Target_TMColumn = (CASE WHEN (@Target_TMColumn IS NULL OR @Target_TMColumn = '') THEN @Source_TMColumn ELSE @Target_TMColumn END);
  SET @WHERE =  (CASE WHEN (@SQLWhere IS NOT NULL AND @SQLWhere !='') THEN (@SQLWhere+' AND ') ELSE 'WHERE' END);
  /*判断@Table数据是否已为最新*/                 
  SET @SQL=N'SELECT @HisDB_maxTM=MAX('+@Target_TMColumn+') FROM'+@TargetTable+' '+@SQLWhere+' ';              
  EXEC sp_executesql @SQL,N'@HisDB_maxTM DATETIME OUTPUT',@HisDB_maxTM OUTPUT;/*计算本地表最新数据时间*/
  IF(@HisDB_maxTM IS NULL)
 BEGIN
  SET @SQL=N'SELECT @HisDB_maxTM=TM FROM OpenQuery('+@srv_lnk+','' SELECT MIN('+@Source_TMColumn+')[TM] FROM'+@SourceTable+' '+@SQLWhere+' '')';             
  EXEC sp_executesql @SQL,N'@HisDB_maxTM DATETIME OUTPUT',@HisDB_maxTM OUTPUT;/*计算出源表最早数据时间*/
 END;
  SET @SQL=N'SELECT @SDB_maxTM=TM FROM OpenQuery('+@srv_lnk+','' SELECT MAX('+@Source_TMColumn+')[TM] FROM'+@SourceTable+' '+@SQLWhere+' '')';             
  EXEC sp_executesql @SQL,N'@SDB_maxTM DATETIME OUTPUT',@SDB_maxTM OUTPUT;/*计算出源表最新数据时间*/
  EXEC('DELETE FROM '+@TargetTable+' '+@WHERE+' '+@Target_TMColumn+' >= =@SDB_maxTM)                   
  BEGIN             
 print('@HisDB_maxTM: '+convert(char(19),@HisDB_maxTM,120)+' <=> @SDB_maxTM: '+convert(char(19),@SDB_maxTM,120));
  SET @SQL = 'INSERT INTO '+@TargetTable+@Target_Columns+' SELECT'+@Source_Columns+' FROM OpenQuery('+@srv_lnk+',                 
   '' SELECT '+@Source_Columns+' FROM '+@SourceTable+' '+@WHERE           
   +' CONVERT(CHAR(19),'+@Source_TMColumn+',120) >= '''''+CONVERT(CHAR(19),@HisDB_maxTM,120)+''''' ORDER BY'+@Source_TMColumn+''')'; 
  EXEC sp_executesql @SQL;                   

  END       
END;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO