比较两条记录中那些字段不相等

yunnet23

免费乐捐，感谢支持

比较两条记录中那些字段不相等，即某个字段的值是否有改变！

例子如下：

select  nGoodsID, sGoodsDesc, sGoodsName, sSpec , sBrand , nPack ,  sUnit ,nCaseUnits ,
       nCategoryID ,sHome ,sNationID , sNation ,sGoodsLevelID ,sGoodsLevel ,
       sGoodTypeID ,sGoodType ,sTradeModeID ,sTradeMode ,sDepositTypeID , sDepositType ,
       nMinLineUnit ,nMinLineL ,nShelfLife ,nMinDueDay ,sNote ,nLength ,nWidth ,
       nHeight ,nWeight ,nCaseLength ,nCaseWidth ,nCaseHeight ,nCaseWeight ,
       dBeginUseDate ,dEndUseDate ,sMemo1 ,sMemo2 ,sMemo3 ,sMemo4 ,sMemo5 ,
       nLineSize ,nSafeStockDay ,nOPLM ,sOrderModeID ,sOrderMode , nSalePrice ,
       nVipPrice,nSaleTaxRate , sMainBarcode ,nTag ,sChangeUser ,dChangeDate ,dLastUpdateTime
  into #Goods001
  from tGoods
 where nGoodsID = 211

select  nGoodsID, sGoodsDesc, sGoodsName, sSpec , sBrand , nPack ,  sUnit ,nCaseUnits ,
       nCategoryID ,sHome ,sNationID , sNation ,sGoodsLevelID ,sGoodsLevel ,
       sGoodTypeID ,sGoodType ,sTradeModeID ,sTradeMode ,sDepositTypeID , sDepositType ,
       nMinLineUnit ,nMinLineL ,nShelfLife ,nMinDueDay ,sNote ,nLength ,nWidth ,
       nHeight ,nWeight ,nCaseLength ,nCaseWidth ,nCaseHeight ,nCaseWeight ,
       dBeginUseDate ,dEndUseDate ,sMemo1 ,sMemo2 ,sMemo3 ,sMemo4 ,sMemo5 ,
       nLineSize ,nSafeStockDay ,nOPLM ,sOrderModeID ,sOrderMode , nSalePrice ,
       nVipPrice,nSaleTaxRate , sMainBarcode ,nTag ,sChangeUser ,dChangeDate ,dLastUpdateTime
  into #Goods002
  from tGoods
 where nGoodsID = 211

select id=identity(4), name ,dd = convert(varchar(255), ''),cc=convert(varchar(255), '')
    into #Goods003 from tempdb..syscolumns where id = object_id('#Goods001')
 
 declare @Num1 int
 declare @i int
 declare @Name varchar(20)
 declare @GoodsID varchar(8)

 select @GoodsID ='211'
 select @i = 1
 select @Num1 = max(id) from #Goods003

while @i < @Num1+ 1
begin
   select @Name = name from #Goods003 where id = @i
   exec ( 'update #Goods003 set dd = convert(varchar(255), (select ' + @Name+ ' from #Goods001 where nGoodsID = convert(numeric(8,0) , ' + @GoodsID + ' ) ) )where name = '''+@Name+''''  )
   exec ( 'update #Goods003 set cc = convert(varchar(255), (select ' + @Name+ ' from #Goods002 where nGoodsID = convert(numeric(8,0) , ' + @GoodsID + ' ) ) )where name = '''+@Name+''''  )
   select @i = @i + 1 
end

select * from #Goods003 where dd<>cc

drop table #Goods001
drop table #Goods002
drop table #Goods003

yunnet23

将结果插入一个新表：

create table tAlterLog(
nID int identity not null,/*ID,序号，没有实际意义*/
dTradeDate datetime not null,/*时间*/
sTableName varchar(30) not null,/*表名*/
sFieldName varchar(30) not null,/*栏位名*/
sOldValue varchar(255) null,/*原值，如果新增资料，此栏位为空*/
sNewValue varchar(255) not null,/*新值*/
sModuleID varchar(20) null,/*产生这条记录的模块编号*/
sVersion varchar(10) null,/*模块对应的版本号*/
sEditer varchar(20) null,/*产生这条记录的操作人*/
constraint PK_ALTERLOG primary key clustered (nID)
on "default"

)
go