点击查看代码
USE []
GO
/****** Object: StoredProcedure [dbo].[Proc_Comapre_Table_Content] Script Date: 2022/7/25 18:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Proc_Comapre_Table_Content]
@TableName1 nvarchar(64),
@TableName2 nvarchar(64),
@ExceptCol nvarchar(256) = '',
@DbName1 nvarchar(64) = '',
@DbName2 nvarchar(64) = '',
@CompareCount int = 30000
AS
--------------------------------
-- 1.check
--------------------------------
-- ErrorCode : 10001 Message: 表不存在
-- ErrorCode : 10002 Message: 表结构不一致
Declare @CheckSql nvarchar(max)
Declare @CompareId nvarchar(64) -- 比较ID
Select @CompareId = newId()
Declare @CompareTime datetime = getdate() -- 比较时间
Declare @CompareResult nvarchar(1) -- 比较结果
Declare @CompareErrorCode nvarchar(10) -- 错误Code
Declare @CompareErrorMessage nvarchar(10) -- 错误信息
-- 获取需要比较的列
DeClare @CompareCol1 nvarchar(max)
DeClare @CompareCol2 nvarchar(max)
Select @CompareCol1 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName1
and COLUMN_NAME not in (''''+ @ExceptCol +'''')
FOR XML PATH('')), 1, 1, '')
Select @CompareCol2 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName2
and COLUMN_NAME not in (''''+ @ExceptCol +'''')
FOR XML PATH('')), 1, 1, '')
-- 表结构比对
IF @CompareCol1 <> @CompareCol2
BEGIN
Set @CompareResult = '2'
Set @CompareErrorCode = 'ErrCd10001'
Set @CompareErrorMessage = 'Table structure is not same .'
Goto SaveErrorResult
END
--------------------------------
-- 2.compare
--------------------------------
Declare @CompareSql nvarchar(max)
Declare @CompareCountSql nvarchar(max)
Declare @DifferentCount int
Set @CompareSql = 'Select ' + @CompareCol1 + ' from ' + @TableName1 + ' except ' + ' Select ' + @CompareCol2 + ' from ' + @TableName2
Set @CompareCountSql = N'Select @DifferentCount = Count(*) from (' + @CompareSql + N')t'
Exec sys.sp_executesql @CompareCountSql, N'@DifferentCount int OUTPUT' , @DifferentCount OUTPUT ;
-- 判断比对结果
IF @DifferentCount = 0
BEGIN
Set @CompareResult = '0'
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult1
END
ELSE
BEGIN
Set @CompareResult = '1'
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult2
END
-- EXEC (@CompareSql)
--------------------------------
-- 3.Save Result
--------------------------------
SaveErrorResult:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@ExceptCol,@DbName1,@DbName2,@CompareCount,getdate())
Return
SaveSuccessedResult1:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@ExceptCol,@DbName1,@DbName2,@CompareCount,getdate())
Return
SaveSuccessedResult2:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@ExceptCol,@DbName1,@DbName2,@CompareCount,getdate())
DECLARE @InsertDetailSql nvarchar(max)
Set @InsertDetailSql = N'Insert Into CompareLogDetails
Select @CompareId , CompareItem , ''I'' as DifferentType , (
Select * From
(
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From (' + @CompareSql + ')t
)d2
Where d2.CompareItem = d1.CompareItem
For Json Path
) As TableContent1
, '''' As TableContent2 , getDate() As InsertTime
From (
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From (' + @CompareSql + ')t
)d1
'
Exec sys.sp_executesql @InsertDetailSql,N'@CompareId nvarchar(64)' ,@CompareId = @CompareId;完整版
点击查看代码
ALTER PROCEDURE [dbo].[Proc_Comapre_Table_Content]
@TableName1 nvarchar(64),
@TableName2 nvarchar(64),
@ExceptCol nvarchar(256) = '',
@DbName1 nvarchar(64) = '',
@DbName2 nvarchar(64) = '',
@CompareCount int = 30000
AS
--------------------------------
-- 1.check 表结构对比，可能存在：1.当两表的列名不相等时，判断两表结构、数据不一样
---2.当两表列名、数量相等时，但数据可能存在差异
--------------------------------
-- ErrorCode : 10001 Message: 表不存在
-- ErrorCode : 10002 Message: 表结构不一致
Declare @CheckSql nvarchar(max)
Declare @CompareId nvarchar(64) -- 比较ID
Select @CompareId = newId()
Declare @CompareTime datetime = getdate() -- 比较时间
Declare @CompareResult nvarchar(1) -- 比较结果
Declare @CompareErrorCode nvarchar(10) -- 错误Code
Declare @CompareErrorMessage nvarchar(10) -- 错误信息
-- 获取需要比较的列
DeClare @CompareCol1 nvarchar(max)
DeClare @CompareCol2 nvarchar(max)
Select @CompareCol1 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName1
and COLUMN_NAME not in (''''+ @ExceptCol +'''')
FOR XML PATH('')), 1, 1, '')
Select @CompareCol2 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName2
and COLUMN_NAME not in (''''+ @ExceptCol +'''')
FOR XML PATH('')), 1, 1, '')
---获取两表的列名且不为空（' '）
-- 表结构比对
IF @CompareCol1 <> @CompareCol2
----当两表的列名不相等时，判断两表结构、数据不一样
BEGIN
Set @CompareResult = '2'
---结果赋值2代表两表数据结构不一样
Set @CompareErrorCode = 'ErrCd10001'
Set @CompareErrorMessage = 'Table structure is not same .'
----表结构不一样
Goto SaveErrorResult
----跳转到SaveErrorResult，将判断结果导入CompareLogHeader表中
END
--------------------------------
-- 2.compare 表数据对比 可能存在： 1.两表数据完全一样，不存在差异。
-----2.两表数据不一样，纯在差异。
--------------------------------
Declare @CompareSql nvarchar(max)
---两表except得出差异数据
Declare @CompareCountSql nvarchar(max)
---count差异数据列数
Declare @DifferentCount int
---差异数据列数
Set @CompareSql = 'Select ' + @CompareCol1 + ' from ' + @TableName1 + ' except ' + ' Select ' + @CompareCol2 + ' from ' + @TableName2
Set @CompareCountSql = N'Select @DifferentCount = Count(*) from (' + @CompareSql + N')t'
Exec sys.sp_executesql @CompareCountSql, N'@DifferentCount int OUTPUT' , @DifferentCount OUTPUT ;
-- 判断比对结果
IF @DifferentCount = 0
--- 当差异数据列数=0时，说明无差异数据
BEGIN
Set @CompareResult = '0'
---赋值0代表无差异数据
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult1
---跳转执行SaveSuccessedResult1将判断结果导入CompareLogHeader表中
END
ELSE
BEGIN
Set @CompareResult = '1'
---赋值1代表有差异数据
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult2
---跳转执行SaveSuccessedResult2将判断结果导入CompareLogHeader表中
END
-- EXEC (@CompareSql)
--------------------------------
-- 3.Save Result 比对结果，根据不同的可能结果，输出对应的表
--------------------------------
SaveErrorResult:
--两表结构、数据不一样得到下表
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@ExceptCol,@DbName1,@DbName2,@CompareCount,getdate())
Return
SaveSuccessedResult1:
--无差异数据时，得到下表
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@ExceptCol,@DbName1,@DbName2,@CompareCount,getdate())
Return
SaveSuccessedResult2:
---当有两表结构一致存在差异数据是得到下表
-- 将主键保存在临时表中
Declare @ConditionSql NVARCHAR(max)
Declare @TypeSql NVARCHAR(256)
Declare @TableContent2 NVARCHAR(256)
Declare @ICount NVARCHAR(max) = 0
Declare @Index NVARCHAR(256)
--获取@TableName1的主键放入临时表中
create Table #Temp_Indexs(indexs nvarchar(50))
Exec ('Insert Into #Temp_Indexs SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME=''' + @TableName1 + '''')
-- 声明游标
DECLARE Indexs CURSOR FAST_FORWARD FOR
SELECT *
FROM #Temp_Indexs
---游标遍历临时表获取主键名
OPEN Indexs;
-- 取第一条记录
FETCH NEXT FROM Indexs INTO @Index;
WHILE @@FETCH_STATUS=0
BEGIN
If @ICount = 0 ---当 If @ICount = 0 时 只有一条数据取 join 条件为 on a.x=b.x
BEGIN
Set @ConditionSql = ' d1.' + @Index + '= d3.' + @Index
Set @TypeSql = 'd3.' + @Index
END
Else
BEGIN Set @ConditionSql = ' AND d1.' + @Index + '= d3.' + @Index END
---当 If @ICount <> 0 时 取第一条数据取 join 条件为 on a.x=b.x 第二条是为 and a.y=b.y
-- 取下一条记录
FETCH NEXT FROM Indexs INTO @Index;
-- 操作
Set @ICount = @ICount +1 --计数，方便判断取第几条数据
END
-- 关闭游标
CLOSE Indexs;
-- 释放游标
DEALLOCATE Indexs;
---判断'I'还是'U'是根据最后 差异数据 left join TableName2 主键列名 后是否有数据来判断的
---最后的查询判断 TableName2.主键列名 为空 就是没join上 说明 TableName2里没有数据 判断为 插入 反之就是更新
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@ExceptCol,@DbName1,@DbName2,@CompareCount,getdate())
DECLARE @InsertDetailSql nvarchar(max)
Set @InsertDetailSql = N'Insert Into CompareLogDetails
Select @CompareId , CompareItem ,' + 'case when ' + @TypeSql + ' is null then ''I'' else ''U'' end as DifferentType ,
(Select * From
(
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From (' + @CompareSql + ')t
)d2
Where d2.CompareItem = d1.CompareItem
For Json Path
) As TableContent1
,
(Select * From
(
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From ' + @TableName2 + ' t
)d3
Where ' + @ConditionSql + '
For Json Path
)
As TableContent2 , getDate() As InsertTime
From (
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From (' + @CompareSql + ')t
)d1
left join ' + @TableName2 + ' d3 on ' + @ConditionSql
Exec sys.sp_executesql @InsertDetailSql,N'@CompareId nvarchar(64)' ,@CompareId = @CompareId;点击查看代码
CREATE PROCEDURE [dbo].[Proc_Comapre_Table_Content]
@TableName1 nvarchar(64),
@TableName2 nvarchar(64),
@CompareCol nvarchar(4000) = '',
@DbName1 nvarchar(64) = '',
@DbName2 nvarchar(64) = '',
@JobName nvarchar(64) = '',
@CompareCount int = 30000
AS
--------------------------------
-- 1.check
--------------------------------
-- ErrorCode : 10001 Message: 表不存在
-- ErrorCode : 10002 Message: 表结构不一致
Declare @CheckSql nvarchar(max)
Declare @CompareId nvarchar(64) -- 比较ID
Select @CompareId = newId()
Declare @CompareTime datetime = getdate() -- 比较时间
Declare @CompareResult nvarchar(1) -- 比较结果
Declare @CompareErrorCode nvarchar(10) -- 错误Code
Declare @CompareErrorMessage nvarchar(10) -- 错误信息
-- 获取需要比较的列
DeClare @CompareCol1 nvarchar(max)
DeClare @CompareCol2 nvarchar(max)
--Select @CompareCol1 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName1
-- and COLUMN_NAME not in (''''+ @ExceptCol +'''')
-- FOR XML PATH('')), 1, 1, '')
--Select @CompareCol2 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName2
-- and COLUMN_NAME not in (''''+ @ExceptCol +'''')
-- FOR XML PATH('')), 1, 1, '')
---- 表结构比对
--IF @CompareCol1 <> @CompareCol2
-- BEGIN
-- Set @CompareResult = '2'
-- Set @CompareErrorCode = 'ErrCd10001'
-- Set @CompareErrorMessage = 'Table structure is not same .'
-- Goto SaveErrorResult
-- END
--------------------------------
-- 2.compare
--------------------------------
Declare @CompareSql nvarchar(max)
Declare @CompareCountSql nvarchar(max)
Declare @DifferentCount int
Set @CompareSql = 'Select ' + @CompareCol + ' from ' + @TableName1 + ' except ' + ' Select ' + @CompareCol + ' from ' + @TableName2
Set @CompareCountSql = N'Select @DifferentCount = Count(*) from (' + @CompareSql + N')t'
Exec sys.sp_executesql @CompareCountSql, N'@DifferentCount int OUTPUT' , @DifferentCount OUTPUT ;
-- 判断比对结果
IF @DifferentCount = 0
BEGIN
Set @CompareResult = '0'
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult1
END
ELSE
BEGIN
Set @CompareResult = '1'
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult2
END
-- EXEC (@CompareSql)
--------------------------------
-- 3.Save Result
--------------------------------
SaveErrorResult:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@CompareCol,@DbName1,@DbName2,@CompareCount,getdate())
Return
SaveSuccessedResult1:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@CompareCol,@DbName1,@DbName2,@CompareCount,getdate())
Return
SaveSuccessedResult2:
-- 将主键保存在临时表中
Declare @ConditionSql NVARCHAR(max)
Declare @TypeSql NVARCHAR(256)
Declare @TableContent2 NVARCHAR(256)
Declare @ICount NVARCHAR(max) = 0
Declare @Index NVARCHAR(256)
create Table #Temp_Indexs(indexs nvarchar(50))
Exec ('Insert Into #Temp_Indexs SELECT?COLUMN_NAME?FROM?INFORMATION_SCHEMA.KEY_COLUMN_USAGE? ?
WHERE?TABLE_NAME=''' + @TableName2 + '''')
-- 声明游标
DECLARE Indexs CURSOR FAST_FORWARD FOR
SELECT *
FROM #Temp_Indexs
OPEN Indexs;
-- 取第一条记录
FETCH NEXT FROM Indexs INTO @Index;
WHILE @@FETCH_STATUS=0
BEGIN
If @ICount = 0
BEGIN
Set @ConditionSql = ' d1.' + @Index + '= d3.' + @Index
Set @TypeSql = 'd3.' + @Index
END
Else
BEGIN Set @ConditionSql = @ConditionSql + ' AND d1.' + @Index + '= d3.' + @Index END
-- 取下一条记录
FETCH NEXT FROM Indexs INTO @Index;
-- 操作
Set @ICount = @ICount +1
END
-- 关闭游标
CLOSE Indexs;
-- 释放游标
DEALLOCATE Indexs;
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@CompareCol,@DbName1,@DbName2,@CompareCount,getdate())
DECLARE @InsertDetailSql nvarchar(max)
Set @InsertDetailSql = N'Insert Into CompareLogDetails
Select @CompareId , CompareItem ,' + 'case when ' + @TypeSql + ' is null then ''I'' else ''U'' end as DifferentType ,
(Select * From
(
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From (' + @CompareSql + ')t
)d2
Where d2.CompareItem = d1.CompareItem
For Json Path
) As TableContent1
,
(Select * From
(
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From ' + @TableName2 + ' t
)d3
Where ' + @ConditionSql + '
For Json Path
)
As TableContent2 , '''' as ExcuteSql , getDate() As InsertTime
From (
Select ROW_NUMBER() over(order by (select 1)) as CompareItem , * From (' + @CompareSql + ')t
)d1
left join ' + @TableName2 + ' d3 on ' + @ConditionSql
Exec sys.sp_executesql @InsertDetailSql,N'@CompareId nvarchar(64)' ,@CompareId = @CompareId;
--------------------------------
-- 4.Update Target
--------------------------------
-- 定义Merge 动态语句
Declare @MergeSql Nvarchar(Max)
Set @MergeSql = N'MERGE INTO ' + @TableName2 + N' d1 ' + char(13)+char(10)+
N' USING (' + @CompareSql + N') d3' + char(13)+char(10)+
N' ON (' + @ConditionSql + N') ' + char(13)+char(10)+
N' WHEN MATCHED THEN ' + char(13)+char(10)
-- 定义更新\插入部分的语句
Declare @UpdateSql Nvarchar(Max)
Declare @InsertSourceSql Nvarchar(Max)
Declare @InsertTargetSql Nvarchar(Max)
Declare @Col NVARCHAR(256)
Set @ICount = 0
-- 定义临时表保存列名
create Table #Temp_Col(Col nvarchar(50))
Exec ('Insert Into #Temp_Col SELECT?value?FROM?STRING_SPLIT('''+@CompareCol + ''', '','')')
-- 声明游标
DECLARE Cols CURSOR FAST_FORWARD FOR
SELECT *
FROM #Temp_Col
OPEN Cols;
-- 取第一条记录
FETCH NEXT FROM Cols INTO @Col;
WHILE @@FETCH_STATUS=0
BEGIN
If @ICount = 0
BEGIN
Set @UpdateSql = ' d1.' + @Col + '= d3.' + @Col
-- Set @InsertSourceSql = ' d1.' + @Col
-- Set @InsertTargetSql = ' d3.' + @Col
--Set @TypeSql = 'd3.' + @Index
END
Else
BEGIN Set @UpdateSql = @UpdateSql + ' ,d1.' + @Col + '= d3.' + @Col
-- Set @InsertSourceSql = @InsertSourceSql + ' ,d1.' + @Col
-- Set @InsertTargetSql = @InsertTargetSql + ' ,d3.' + @Col
END
-- 取下一条记录
FETCH NEXT FROM Cols INTO @Col;
-- 操作
Set @ICount = @ICount +1
END
-- 关闭游标
CLOSE Cols;
-- 释放游标
DEALLOCATE Cols;
-- 将更新、插入逻辑加到Merge 逻辑中
Set @MergeSql = @MergeSql + N' UPDATE SET ' + char(13)+char(10)+
@UpdateSql + char(13)+char(10)+
N' WHEN NOT MATCHED THEN ' + char(13)+char(10)+
N'Insert VALUES ('
+ char(13)+char(10)+ @CompareCol
+ N',getdate(),getdate(),'''''
+ N' ); '
-- 更新目标表
Exec( @MergeSql)点击查看代码
CREATE PROCEDURE [dbo].[Proc_Comapre_Table_Content]
@TableName1 nvarchar(64),
@TableName2 nvarchar(64),
@ConditionForTableName1 nvarchar(4000) = '',
@ConditionForTableName2 nvarchar(4000) = '',
@CompareCol nvarchar(MAX) ,
@PrimaryCol nvarchar(4000) = '',
@ReserveCol1 nvarchar(64) = '',
@ReserveCol2 nvarchar(64) = '',
@ReserveCol3 nvarchar(64) = '',
@ReserveCol4 nvarchar(64) = '',
-- @DbName1 nvarchar(64) = '',
-- @DbName2 nvarchar(64) = '',
@JobName nvarchar(64) = '',
@IsUpdateDestFlg nvarchar(64) = 0,
@Excutor nvarchar(64) = 'DW'
--@CompareCount int = 30000
AS
/*
参数说明：
@TableName1 源表名
@TableName2 目标表名
@ConditionForTableName1 源表条件限制，可选
如： @ConditionForTableName1 = N' AND Name = N''南京药石科技股份有限公司''',
@ConditionForTableName2 目标表条件限制，可选
@CompareCol 比对列
如：@CompareCol = N'Name,RegStatus,City',
@PrimaryCol 主键列（如为空则取表实际主键），可选
如：@PrimaryCol = N'Name',
@ReserveCol1 保留列1 ，可选
如： @ReserveCol1 = N'Name',
@ReserveCol2 保留列2，可选
@ReserveCol3 保留列3，可选
@ReserveCol4 保留列4，可选
@JobName 调用方作业名称，默认为空
@IsUpdateDestFlg 是否更新目标表,默认不更新
@Excutor 执行方，取值范围（BP:业务平台,DW：数仓）
*/
--------------------------------
-- 1.check
--------------------------------
-- ErrorCode : 10001 Message: 表不存在
-- ErrorCode : 10002 Message: 表结构不一致
Declare @CheckSql nvarchar(max)
Declare @CompareId nvarchar(64) -- 比较ID
Select @CompareId = newId()
Declare @CompareTime datetime = getdate() -- 比较时间
Declare @CompareResult nvarchar(1) -- 比较结果
Declare @CompareErrorCode nvarchar(10) -- 错误Code
Declare @CompareErrorMessage nvarchar(10) -- 错误信息
-- 获取需要比较的列
DeClare @CompareCol1 nvarchar(max)
DeClare @CompareCol2 nvarchar(max)
--Select @CompareCol1 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName1
-- and COLUMN_NAME not in (''''+ @ExceptCol +'''')
-- FOR XML PATH('')), 1, 1, '')
--Select @CompareCol2 = STUFF((select ',' + COLUMN_name from INFORMATION_SCHEMA.columns where TABLE_NAME = @TableName2
-- and COLUMN_NAME not in (''''+ @ExceptCol +'''')
-- FOR XML PATH('')), 1, 1, '')
---- 表结构比对
--IF @CompareCol1 <> @CompareCol2
-- BEGIN
-- Set @CompareResult = '2'
-- Set @CompareErrorCode = 'ErrCd10001'
-- Set @CompareErrorMessage = 'Table structure is not same .'
-- Goto SaveErrorResult
-- END
--------------------------------
-- 2.compare
--------------------------------
Declare @CompareSql nvarchar(max)
Declare @CompareSql2 nvarchar(max)
Declare @CompareCountSql nvarchar(max)
Declare @DifferentCount int
Set @CompareSql = 'Select ' + @CompareCol + ' from ' + @TableName1 + char(13)+char(10)+
' Where 1=1 ' + @ConditionForTableName1 + char(13)+char(10)+
+ ' except ' + ' Select ' + @CompareCol + ' from ' + @TableName2 + char(13)+char(10)+
' Where 1=1 ' + @ConditionForTableName2
Set @CompareSql2 = 'Select ' + @CompareCol + ' from ' + @TableName2 + char(13)+char(10)+
' Where 1=1 ' + @ConditionForTableName2 + char(13)+char(10)+
+ ' except ' + ' Select ' + @CompareCol + ' from ' + @TableName1 + char(13)+char(10)+
' Where 1=1 ' + @ConditionForTableName1
Set @CompareCountSql = N'Select @DifferentCount = Count(*) from (' + @CompareSql + N')t'
Exec sys.sp_executesql @CompareCountSql, N'@DifferentCount int OUTPUT' , @DifferentCount OUTPUT ;
-- 判断比对结果
IF @DifferentCount = 0
BEGIN
Set @CompareResult = '0'
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult1
END
ELSE
BEGIN
Set @CompareResult = '1'
Set @CompareErrorCode = ''
Set @CompareErrorMessage = ''
GOTO SaveSuccessedResult2
END
-- EXEC (@CompareSql)
--------------------------------
-- 3.Save Result
--------------------------------
SaveErrorResult:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@CompareCol,@Excutor,0,getdate())
Return
SaveSuccessedResult1:
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@CompareCol,@Excutor,0,getdate())
Return
SaveSuccessedResult2:
-- 将主键保存在临时表中
Declare @ConditionSql NVARCHAR(max)
Declare @TypeSql NVARCHAR(256)
Declare @TableContent2 NVARCHAR(256)
Declare @ICount NVARCHAR(max) = 0
Declare @Index NVARCHAR(256)
create Table #Temp_Indexs(indexs nvarchar(50))
-- 如果主键参数是空的情况
If @PrimaryCol = ''
Begin
Exec ('Insert Into #Temp_Indexs SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME=''' + @TableName2 + '''')
End
Else
Begin
Exec ('Insert Into #Temp_Indexs SELECT Value FROM String_Split(''' + @PrimaryCol +''','','')')
End
-- 声明游标
DECLARE Indexs CURSOR FAST_FORWARD FOR
SELECT *
FROM #Temp_Indexs
OPEN Indexs;
-- 取第一条记录
FETCH NEXT FROM Indexs INTO @Index;
WHILE @@FETCH_STATUS=0
BEGIN
If @ICount = 0
BEGIN
Set @ConditionSql = ' Source.' + @Index + '= Target.' + @Index
Set @TypeSql = 'Target.' + @Index
END
Else
BEGIN Set @ConditionSql = @ConditionSql + ' AND Source.' + @Index + '= Target.' + @Index END
-- 取下一条记录
FETCH NEXT FROM Indexs INTO @Index;
-- 操作
Set @ICount = @ICount +1
END
-- 关闭游标
CLOSE Indexs;
-- 释放游标
DEALLOCATE Indexs;
Insert Into CompareLogHeader values(@CompareId,@CompareTime,@CompareResult,@CompareErrorCode,@CompareErrorMessage,@TableName1
,@TableName2,@CompareCol,@Excutor,0,getdate())
DECLARE @InsertDetailSql nvarchar(max)
-- 表一子查询
DECLARE @Table1Sql nvarchar(max)
-- 表二子查询
DECLARE @Table2Sql nvarchar(max)
Set @Table1Sql = 'Select * from ' + @TableName1 + ' Where 1=1 ' + @ConditionForTableName1
Set @Table2Sql = 'Select * from ' + @TableName2 + ' Where 1=1 ' + @ConditionForTableName2
Set @InsertDetailSql = N'Insert Into CompareLogDetails
Select @CompareId , ROW_NUMBER() over(order by (select 1)) as CompareItem ,' + 'case when ' + @TypeSql + ' is null then ''I'' else ''U'' end as DifferentType ,
(Select * From
(
Select * From （' + @Table1Sql + ')t
)Target
Where ' + @ConditionSql + '
For Json Path
) As TableContent1
,
(Select * From
(
Select * From （' + @Table2Sql + ')t
)Target
Where ' + @ConditionSql + '
For Json Path
)
As TableContent2 ,'+ IIF(@ReserveCol1 <> '' ,'Source.' + @ReserveCol1 + ',' , ''''',' )
+ IIF(@ReserveCol2 <> '' ,'Source.' + @ReserveCol2 + ',' , ''''',' )
+ IIF(@ReserveCol3 <> '' ,'Source.' + @ReserveCol3 + ',' , ''''',' )
+ IIF(@ReserveCol4 <> '' ,'Source.' + @ReserveCol4 + ',' , ''''',' )
+ ''''' as ExcuteSql , getDate() As InsertTime
From (
Select * From (' + @CompareSql + ')t
)Source
left join （ ' + @Table2Sql + ')Target on ' + @ConditionSql
Exec sys.sp_executesql @InsertDetailSql,N'@CompareId nvarchar(64)' ,@CompareId = @CompareId;
-- 获取数据删除的部分
-- 将表1和表2的顺序换一下
--Set @InsertDetailSql = REPLACE(@InsertDetailSql,@TableName1+' ','@TableName2 ')
--Set @InsertDetailSql = REPLACE(@InsertDetailSql,@TableName2,@TableName1)
--Set @InsertDetailSql = REPLACE(@InsertDetailSql,'@TableName2',@TableName2)
-- 将子查询换一下
Set @InsertDetailSql = REPLACE(@InsertDetailSql,@Table1Sql,'@Table2Sql')
Set @InsertDetailSql = REPLACE(@InsertDetailSql,@Table2Sql,@Table1Sql)
Set @InsertDetailSql = REPLACE(@InsertDetailSql,'@Table2Sql',@Table2Sql)
Set @InsertDetailSql = REPLACE(@InsertDetailSql,@CompareSql,@CompareSql2)
-- 将类型I 改为 D
Set @InsertDetailSql = REPLACE(@InsertDetailSql,'''I''','''D''')
-- 限制只取类型为I的类型
Set @InsertDetailSql = @InsertDetailSql + char(13)+char(10)+
' Where '+ 'case when ' + @TypeSql + ' is null then ''I'' else ''U'' end = ''I'' '
Exec sys.sp_executesql @InsertDetailSql,N'@CompareId nvarchar(64)' ,@CompareId = @CompareId;
--------------------------------
-- 4.Update Target
--------------------------------
-- 定义Merge 动态语句
If @IsUpdateDestFlg = 0
Begin
Return
End
Declare @MergeSql Nvarchar(Max)
Set @MergeSql = N'MERGE INTO ' + @TableName2 + N' Source ' + char(13)+char(10)+
N' USING ' + @TableName1 + N' Target' + char(13)+char(10)+
N' ON (' + @ConditionSql + N') ' + char(13)+char(10)+
N' WHEN MATCHED THEN ' + char(13)+char(10)
-- 定义更新\插入部分的语句
Declare @UpdateSql Nvarchar(Max)
Declare @InsertSourceSql Nvarchar(Max)
Declare @InsertTargetSql Nvarchar(Max)
Declare @Col NVARCHAR(256)
Set @ICount = 0
-- 定义临时表保存列名
create Table #Temp_Col(Col nvarchar(50))
Exec ('Insert Into #Temp_Col SELECT value FROM STRING_SPLIT('''+@CompareCol + ''', '','')')
-- 声明游标
DECLARE Cols CURSOR FAST_FORWARD FOR
SELECT *
FROM #Temp_Col
OPEN Cols;
-- 取第一条记录
FETCH NEXT FROM Cols INTO @Col;
WHILE @@FETCH_STATUS=0
BEGIN
If @ICount = 0
BEGIN
Set @UpdateSql = ' Source.' + @Col + '= Target.' + @Col
-- Set @InsertSourceSql = ' d1.' + @Col
-- Set @InsertTargetSql = ' d3.' + @Col
--Set @TypeSql = 'd3.' + @Index
END
Else
BEGIN Set @UpdateSql = @UpdateSql + ' ,Source.' + @Col + '= Target.' + @Col
-- Set @InsertSourceSql = @InsertSourceSql + ' ,d1.' + @Col
-- Set @InsertTargetSql = @InsertTargetSql + ' ,d3.' + @Col
END
-- 取下一条记录
FETCH NEXT FROM Cols INTO @Col;
-- 操作
Set @ICount = @ICount +1
END
-- 关闭游标
CLOSE Cols;
-- 释放游标
DEALLOCATE Cols;
-- 将更新、插入逻辑加到Merge 逻辑中
Set @MergeSql = @MergeSql + N' UPDATE SET ' + char(13)+char(10)+
@UpdateSql + char(13)+char(10)+
N' WHEN NOT MATCHED THEN ' + char(13)+char(10)+
N'Insert VALUES ('
+ char(13)+char(10)+ @CompareCol
+ N',getdate(),getdate(),'''''
+ N' )'+ char(13)+char(10)+
+ 'When Not Matched By Source '+ char(13)+char(10)+
+ 'THEN DELETE ;';
-- 更新目标表
Exec( @MergeSql)本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
