通过链接服务器查询并插入Spc_CheckReplDiff

 

通过链接服务器同步数据的存储过程

 

/****** Object:  StoredProcedure [dbo].[Spc_CheckReplDiff]    Script Date: 02/10/2011 09:56:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--declare @RetStatus tinyint,
--		@RetMsg nvarchar(1000)
----exec Spc_CheckReplDiff @TabName ='EC_Product',@CurrentDB ='Dinoweb',@Publisher = N'DINODB\SOFTISLAND01',@DestTabName =N'EC_Product',@SourceDB = N'Dinoweb',@RetStatus=@RetStatus output,@RetMsg=@RetMsg output
--exec Spc_CheckReplDiff @TabName ='EC_Product',@RetStatus=@RetStatus output,@RetMsg=@RetMsg output
----select @RetMsg
--go



Create Proc [dbo].[Spc_CheckReplDiff]
(
	@TabName				nvarchar(50),							--对象表名
	@DestTabName			nvarchar(50)  = @TabName,				--目标表名
	@Publisher				nvarchar(100) = N'SOFTISLAND',			--链接数据库实例名
	@SourceDB				nvarchar(50)  = N'DB',					--目标数据库
	@CurrentDB				nvarchar(50)  = N'',					--当前数据库
	@RetStatus				tinyint output,							--执行结果
	@RetMsg					nvarchar(max) output					--执行结果
)
as
Begin
	Declare @HASIDENTY		BIT,									--是否有自增列
			@HASTAB			BIT =0,									--是否存在该对象
			@SQLStr			nvarchar(2000),							--执行SQL字符串
			@SQLStrTemp		nvarchar(200),							--临时SQL字符串
			@LinkSrvName	nvarchar(100),							--链接服务器
			@ColList		nvarchar(max),							--插入列名
			@PrimaryKeyList nvarchar(1000),							--主键列名
			@StrSQLN		nvarchar(max),							--主键列名
			@SETIDENTYStart nvarchar(100),							--自增列设置开启
			@SETIDENTYEnd	nvarchar(100)							--自增列设置关闭
	SET NOCOUNT ON
	BEGIN TRY	
		SET @RetStatus = 0;
		SET @RetMsg = N'SUCCESS';
		SET @CurrentDB = DB_NAME();
		SET @SETIDENTYStart = '';
		SET @SETIDENTYEnd = '';
		
		SET @SQLStr= 'SELECT @HASTAB = 1
						FROM '+ @CurrentDB + '.sys.objects WITH(NOLOCK)
						WHERE name = ''' + @TabName + ''''
						
		Exec sp_executesql @SQLStr,N'@HASTAB BIT Output',@HASTAB output
		IF(@HASTAB =0)
		BEGIN
			SET @RetStatus = 1;
			SET @RetMsg = N'在目标数据库没有这个表--' + @TabName;
			--print @RetMsg;
			RETURN;
		END
		
		--获取链接服务器
		SET @LinkSrvName = N'';
		
		SET @SQLStr= N' SELECT Top 1 @LinkSrvName = Name FROM master.sys.servers WITH(NOLOCK)
		where is_linked = 1
		and is_data_access_enabled =1
		and uses_remote_collation = 1
		and data_source = N''' + @Publisher+''''
		
		Exec sp_executesql @SQLStr,N'@LinkSrvName nvarchar(100) Output',@LinkSrvName output
		IF(@LinkSrvName = N'')
		BEGIN
			SET @RetStatus = 2;
			SET @RetMsg = N'在目标数据库无链接服务器到--' + @Publisher;
			RETURN;
		END
	
		--判断是否有自增列属性
		SET @SQLStr= 'SELECT @HASIDENTY = 1
		FROM '+ @CurrentDB + '.sys.objects r WITH(NOLOCK) left outer join ' + @CurrentDB + '.sys.columns as cl WITH(NOLOCK)
		on r.object_id = cl.object_id
		WHERE cl.is_identity = 1
		AND r.name = ''' + @TabName + ''''
		
		SET @RetStatus = 3;
		SET @RetMsg = N'判断是否有自增列属性';
		
		Exec sp_executesql @SQLStr,N'@HASIDENTY int Output',@HASIDENTY output
		
		--获取对象表列名
		
		SET @SQLStrTemp = ',';
		SET @ColList = '';
		SET @SQLStr= 'SELECT @ColList = @ColList + Name +''' + @SQLStrTemp + ''' FROM ' + @CurrentDB + '.sys.columns WITH(NOLOCK)
		where object_id = OBJECT_ID('''+@CurrentDB + '.dbo.' + @TabName+''')'

		SET @RetStatus = 4;
		SET @RetMsg = N'获取对象表列名';
		
		Exec sp_executesql @SQLStr,N'@ColList nvarchar(max) Output',@ColList output
		
		SET @ColList = SubString(@ColList, 1, Len(@ColList) - LEN(@SQLStrTemp));
		
		--获取对象主键
		
		SET @SQLStrTemp = ' And ';
		SET @PrimaryKeyList = '';
		
		SET @SQLStr = N'SELECT @PrimaryKeyList = @PrimaryKeyList + C.name+CASE WHEN C.collation_name IS NOT NULL THEN N'' COLLATE Chinese_PRC_CI_AS '' ELSE N'''' END +''= A.''+C.name+CASE WHEN C.collation_name IS NOT NULL THEN N'' COLLATE Chinese_PRC_CI_AS '' ELSE N'''' END +'''+@SQLStrTemp+'''
						FROM sys.indexes A WITH (NOLOCK),sys.index_columns B WITH (NOLOCK),sys.columns C WITH (NOLOCK)
						WHERE A.is_primary_key =1
						AND A.object_id = B.object_id
						AND A.index_id = B.index_id
						AND B.object_id = C.object_id
						AND B.column_id = C.column_id
						AND A.object_id = OBJECT_ID(''' + @CurrentDB + '.dbo.' + @TabName+''')'
		
		SET @RetStatus = 5;
		SET @RetMsg = N'获取对象主键';
						
		Exec sp_executesql @SQLStr,N'@PrimaryKeyList nvarchar(1000) Output',@PrimaryKeyList output;
		
		SET @PrimaryKeyList = SubString(@PrimaryKeyList, 1, Len(@PrimaryKeyList) - LEN(@SQLStrTemp));
		--判断是否有自增属性,有则开启 IDENTITY_INSERT
		IF(@HASIDENTY =1)
		BEGIN
			SET @SETIDENTYStart = 'SET IDENTITY_INSERT '+@CurrentDB+'.dbo.'+@TabName+' ON';
			SET @SETIDENTYEnd = 'SET IDENTITY_INSERT '+@CurrentDB+'.dbo.'+@TabName+' OFF';
		END
		
		SET @RetStatus = 6;
		SET @RetMsg = N'执行插入';
		--生成插入语句
		SET @StrSQLN = 'INSERT INTO ' + @CurrentDB + '.dbo.' + @TabName + '(' + @ColList + ')
							SELECT ' + @ColList+'
							FROM ' + @LinkSrvName + '.' + @SourceDB + '.dbo.' + @DestTabName+' AS A
							WHERE NOT EXISTS
							(
								SELECT 1 FROM ' + @CurrentDB + '.dbo.' + @TabName + '
								WHERE '+ @PrimaryKeyList + '
							)'
		SET @StrSQLN = @SETIDENTYStart + CHAR(13) + @StrSQLN + CHAR(13) + @SETIDENTYEnd;
		--select @StrSQLN;
		EXECUTE(@StrSQLN);
		SET @RetStatus = 0;
		SET @RetMsg = N'SUCCESS->本次插入表:'+@TabName+' 共->'+Convert(nvarchar(10),@@ROWCOUNT)+' 条记录.';
		Print @RetMsg;
	END TRY	
	BEGIN CATCH
		SET @RetMsg = '在执行中发生错误:' + @RetMsg;
		SET @RetMsg = @RetMsg +CHAR(13)+@StrSQLN;
	END CATCH
	SET NOCOUNT OFF
End