清除脏数据的另一种方法

清除脏数据的另一种方法

--将主表数据导入临时表中,包含工艺路线
select *,dbo.f_pd_GetRoutingDtl(RoutID) As 'RoutingDtl' into #TMPpdRouting from pdRouting
select * from #TMPpdRouting

--将明细表数据导入临时表中
select * into #TMPpdRoutingDtl from pdRoutingDtl
select * from #TMPpdRoutingDtl

--将正确的主表数据插入临时表中
Select ta.* Into #TMPpdRoutingOK From #TMPpdRouting ta,
 (Select Min(RoutID) As 'RoutID',RoutingDtl From #TMPpdRouting Group By RoutingDtl) tb
  Where ta.RoutID = tb.RoutID

--将正确的明细表数据插入临时表中
Select ta.* Into #TMPpdRoutingDtlOK From #TMPpdRoutingDtl ta,
 (Select Min(RoutID) As 'RoutID',RoutingDtl From #TMPpdRouting Group By RoutingDtl) tb
  Where ta.RoutID = tb.RoutID

--查看正确的数据,并将数据插入到原始表中
--主表
Insert Into pdRouting
 Select RoutID,RoutDesc,EnableDate,DisabledDate,GoodsID,
  FCheckFlag,FChecker,FCheckDate,OperatorID,InputDate,Remarks,TextDesc,RoutingTypeID,
  FAprovFlag,FAprover,FAprovDate,FVerifyFlag,FVerifyer,FVerifyDate,VDate,GoodsTypeID,
  ArtTypeID,ArtsRoutTypeID
   From #TMPpdRoutingOK
--明细表
Insert Into pdRoutingDtl Select * From #TMPpdRoutingDtlOK

--查看修改后的数据是否正确
Select Min(RoutID),dbo.f_pd_GetRoutingDtl(RoutID) As 'RoutingDtl' From pdRouting
 Group By dbo.f_pd_GetRoutingDtl(RoutID) Having Count(*) > 1

Select * From pdRouting