通過連結伺服器查詢并插入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',@[email protected] output,@[email protected] output
--exec Spc_CheckReplDiff @TabName ='EC_Product',@[email protected] output,@[email protected] 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('''[email protected] + '.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 +'''[email protected]+'''
						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 '[email protected]+'.dbo.'[email protected]+' ON';
			SET @SETIDENTYEnd = 'SET IDENTITY_INSERT '[email protected]+'.dbo.'[email protected]+' 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->本次插入表:'[email protected]+' 共->'+Convert(nvarchar(10),@@ROWCOUNT)+' 條記錄.';
		Print @RetMsg;
	END TRY	
	BEGIN CATCH
		SET @RetMsg = '在執行中發生錯誤:' + @RetMsg;
		SET @RetMsg = @RetMsg +CHAR(13)[email protected];
	END CATCH
	SET NOCOUNT OFF
End