SQL server 表数据对比

-- =============================================
-- Author:        luomingui
-- Create date: 2020-10-01
-- Description:    表与表的列数据比对
-- =============================================
ALTER PROCEDURE [dbo].[Pro_Com_Wage]
 @table1 nvarchar(50),
 @table2 nvarchar(50)
AS
BEGIN


--获取人事工资项

declare @orginSplit nvarchar(max)
set @orginSplit=''
declare @orginSplit1 nvarchar(max)
set @orginSplit1=''

select @orginSplit+=name+',' from syscolumns Where ID=OBJECT_ID(@table1) and name not in ( '工号','姓名','证件号码','部门名称','部门编号','参加工作时间','职称','工资身份','起薪状态','编制类别','退休日期','工作年限','岗位级别','在岗状态','在职情况','入校时间')
--set @orginSplit1=substring(@orginSplit,0,len(@orginSplit)-1)
 set @orginSplit1=','+@orginSplit
--print @orginSplit1

--获取财务工资项
declare @newSplit nvarchar(max)
set @newSplit=''
declare @newSplit1 nvarchar(max)
set @newSplit1=''


select @newSplit+=name+',' from syscolumns Where ID=OBJECT_ID(@table2) and name not in ( 'Error_info','工号','姓名','岗位分类')
--set @newSplit1=substring(@newSplit,0,len(@newSplit)-1)
set @newSplit1=','+@newSplit


-------begin获取相同的字段------------------

declare @refield nvarchar(max)--相同工资项
set @refield=''
declare @speaterString nvarchar(10)

--set @orginSplit='编制类别,部门编号,部门名称,参加工作时间,调岗日期,妇卫费,岗聘时间,岗位级别,岗资,工号,工资标准提高部分,工资身份,工作年限'
--set @newSplit='编制类别,部门编号,部门名称,参加工作'
set @speaterString=','


   declare @val smallint,@i int,@helpSplit varchar(max)
   set @val=0
   set @newSplit=rtrim(ltrim(@newSplit))
   set @speaterString=rtrim(ltrim(@speaterString))
   set @orginSplit=rtrim(ltrim(@orginSplit))
 if left(@orginSplit,len(@speaterString))<>@speaterString
    set  @orginSplit=@speaterString+@orginSplit
 if right(@orginSplit,len(@speaterString))<>@speaterString
    set  @orginSplit=@orginSplit+@speaterString
if @newSplit=''
   print @val
 if charindex(@speaterString,@newSplit)=0  
   begin
    --或者改成set  @helpSplit=replace(@orginSplit,@speaterString+@newSplit+@speaterString,'')
 -- if len(@speaterString+@newSplit+@speaterString)+len(@helpSplit)=len(@orginSplit)
 set  @helpSplit=@speaterString+@newSplit+@speaterString

 if patindex('%'+@helpSplit+'%',@orginSplit)>0
    set @val=1
   end
 else
    begin
     if right(@newSplit,len(@speaterString))<>@speaterString
    set @newSplit=@newSplit+@speaterString
 set @i=charindex(@speaterString,@newSplit)

 while @i>0
    begin
       if rtrim(ltrim(left(@newSplit,@i-1)))=''
        begin
            set  @newSplit=SUBSTRING(@newSplit,@i+1,LEN(@newSplit)-@i)
            set @i=CHARINDEX(@speaterString,@newSplit)
            continue
        end
    set @helpSplit=@speaterString+rtrim(ltrim(left(@newSplit,@i-1)))+@speaterString
    
  --或者改成  len(@helpSplit)+len(replace(@orginSplit,@helpSplit,''))=len(@orginSplit)

    if patindex('%'+@helpSplit+'%',@orginSplit)>0
     begin
     --print replace(@helpSplit,',','')
     set @refield+='A.['+replace(@helpSplit,',','')+'] as [人事'+replace(@helpSplit,',','')+'],'+'B.['+replace(@helpSplit,',','')+'] as [财务'+replace(@helpSplit,',','')+'],isnull(A.['+replace(@helpSplit,',','')+'],0)-isnull(B.['+replace(@helpSplit,',','')+'],0) as ['+replace(@helpSplit,',','')+'差],'


 set @orginSplit1=replace(@orginSplit1,@helpSplit,',')
 set @newSplit1=replace(@newSplit1,@helpSplit,',')

        set @val=1
        --break
    end
    set  @newSplit=SUBSTRING(@newSplit,@i+1,LEN(@newSplit)-@i)
    set @i=CHARINDEX(@speaterString,@newSplit)
    end
    end  
    
    
    --set @refield=substring(@refield,0,len(@refield)-1)
 
 
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')
set @orginSplit1=replace(@orginSplit1,',,',',')

 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')
 set @newSplit1=replace(@newSplit1,',,',',')


 --print @orginSplit1
 --print @newSplit1
 --print @refield
-------end获取相同的字段------------------

declare @sql nvarchar(max)


set @orginSplit1='['+replace(@orginSplit1,',','],[')
set @orginSplit1=substring(@orginSplit1,4,len(@orginSplit1)-4)

set @newSplit1='['+replace(@newSplit1,',','],[')
set @newSplit1=substring(@newSplit1,4,len(@newSplit1)-5)


set @sql=' select A.[工号],A.[姓名],A.[部门名称],'+substring(@refield,1,len(@refield)-1) +'  from '+@table1+' as A full join '+@table2+' as B on A.工号=B.工号  where B.[工号] is not null'

--print @sql
exec(@sql)
END

调用 效果图：exec [Pro_Com_Wage] 'Detail_Wage202108','Detail_Wages_Import_ZZGZB202108'