ALTER PROCEDURE Proc_DelOLDDataMatchingNewData(
@Model AS int
)
AS
Begin
Set NOCOUNT ON
CREATE TABLE #tm_Product_tree(
OrderID Decimal,
)
CREATE TABLE #del_Part(
OrderID Decimal,
)
DECLARE @ProductID AS decimal
DECLARE @OrderID AS decimal
DECLARE @PartID AS decimal
DECLARE @FatherOrderID AS decimal
DECLARE @RoughID AS decimal
DECLARE @NewOrderID AS decimal
DECLARE @NewPartID AS decimal
DECLARE @NewRoughID AS decimal
DECLARE @NewProductID AS decimal
DECLARE @PartName AS nvarchar(100)
DECLARE @PartPicNO AS nvarchar(100)
DECLARE @IsDepot AS nvarchar(100)
DECLARE @IsWorkshop AS nvarchar(100) --========================================================================================================
IF @Model=0 Or @Model=1
Begin
--========================================================================================================
Delete From [tm_PublicationMaterialInfo_Config]
Where (IsDepot Is Null Or IsDepot='')
And (IsWorkshop Is Null Or IsWorkshop='')
And (PartName Is Null Or PartName='')
And (PartPicNo Is Null Or PartPicNo='')
And (Spec Is Null Or Spec='')
And (Material Is Null Or Material='')
And (PartCount Is Null Or PartCount='')
And (length Is Null Or length='')
And (TempletName Is Null Or TempletName='')
And (PartBasicMetricUnit Is Null Or PartBasicMetricUnit='')
And (Comment Is Null Or Comment='')
And (ClassName Is Null Or ClassName='')
And (MergeSign Is Null)
And (WorkMergeSign Is Null)
And (CollectPicNo Is Null Or CollectPicNo='')
And (CollectSpec Is Null Or CollectSpec='')
And (IsTube Is Null) --========================================================================================================
WHILE EXIsTS (Select Top 1 OrderID From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])))
Begin
Delete From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])
)
End
Delete From [技术网删除的零件] Where OrderID Not in (Select OrderID From [tm_Product_tree]) --========================================================================================================
Delete From #del_Part
Delete From #tm_Product_tree
DECLARE Cursor_Rough SCROLL CURSOr
FOr Select a.ProductID,a.OrderID,a.FatherOrderID,b.PartName,b.PartPicNO ,c.IsDepot,c.IsWorkshop
From [技术网删除的零件] as a
inner join [tm_Part_info] as b On a.PartID=b.PartID
inner join [tm_PublicationMaterialInfo_Config] as c ON a.OrderID=c.OrderID
OPEN Cursor_Rough
FETCH FIRST From Cursor_Rough Into @ProductID,@OrderID,@FatherOrderID,@PartName,@PartPicNO,@IsDepot,@IsWorkshop
While @@FETCH_STATUS = 0
Begin
Delete From #tm_Product_tree
Insert Into #tm_Product_tree(OrderID)
Select OrderID
From [view_GetPartInfo]
Where ProductID=@ProductID And OrderID<>@OrderID
And OrderID In (Select OrderID From [技术网添加的零件])
And PartName=@PartName And PartPicNO=@PartPicNO
Update [tm_PublicationMaterialInfo_Config] Set IsDepot=@IsDepot
Where OrderID In (Select OrderID From #tm_Product_tree)
And (IsDepot Is Null Or IsDepot='')
Update [tm_PublicationMaterialInfo_Config] Set IsWorkshop=@IsWorkshop
Where OrderID In (Select OrderID From #tm_Product_tree)
And (IsWorkshop Is Null Or IsWorkshop='')
Insert Into [tm_PublicationMaterialInfo_Config](OrderID,IsDepot,IsWorkshop)
Select OrderID,@IsDepot,@IsWorkshop
From [tm_Product_tree]
Where OrderID In (Select OrderID From #tm_Product_tree)
And OrderID Not In (Select OrderID From [tm_PublicationMaterialInfo_Config])
IF exIsts(Select Top 1 OrderID From #tm_Product_tree)
Begin
Insert Into #del_Part(OrderID)
Values(@OrderID)
End
Print @OrderID
FETCH next From cursOr_Rough Into @ProductID,@OrderID,@FatherOrderID,@PartName,@PartPicNO,@IsDepot,@IsWorkshop
End
CLOSE cursOr_Rough
DEALLOCATE cursOr_Rough
Delete From [tm_Product_tree] Where OrderID in (Select OrderID From #del_Part)
--========================================================================================================
WHILE EXIsTS (Select Top 1 OrderID From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])))
Begin
Delete From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])
)
End
End --========================================================================================================
@Model AS int
)
AS
Begin
Set NOCOUNT ON
CREATE TABLE #tm_Product_tree(
OrderID Decimal,
)
CREATE TABLE #del_Part(
OrderID Decimal,
)
DECLARE @ProductID AS decimal
DECLARE @OrderID AS decimal
DECLARE @PartID AS decimal
DECLARE @FatherOrderID AS decimal
DECLARE @RoughID AS decimal
DECLARE @NewOrderID AS decimal
DECLARE @NewPartID AS decimal
DECLARE @NewRoughID AS decimal
DECLARE @NewProductID AS decimal
DECLARE @PartName AS nvarchar(100)
DECLARE @PartPicNO AS nvarchar(100)
DECLARE @IsDepot AS nvarchar(100)
DECLARE @IsWorkshop AS nvarchar(100) --========================================================================================================
IF @Model=0 Or @Model=1
Begin
--========================================================================================================
Delete From [tm_PublicationMaterialInfo_Config]
Where (IsDepot Is Null Or IsDepot='')
And (IsWorkshop Is Null Or IsWorkshop='')
And (PartName Is Null Or PartName='')
And (PartPicNo Is Null Or PartPicNo='')
And (Spec Is Null Or Spec='')
And (Material Is Null Or Material='')
And (PartCount Is Null Or PartCount='')
And (length Is Null Or length='')
And (TempletName Is Null Or TempletName='')
And (PartBasicMetricUnit Is Null Or PartBasicMetricUnit='')
And (Comment Is Null Or Comment='')
And (ClassName Is Null Or ClassName='')
And (MergeSign Is Null)
And (WorkMergeSign Is Null)
And (CollectPicNo Is Null Or CollectPicNo='')
And (CollectSpec Is Null Or CollectSpec='')
And (IsTube Is Null) --========================================================================================================
WHILE EXIsTS (Select Top 1 OrderID From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])))
Begin
Delete From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])
)
End
Delete From [技术网删除的零件] Where OrderID Not in (Select OrderID From [tm_Product_tree]) --========================================================================================================
Delete From #del_Part
Delete From #tm_Product_tree
DECLARE Cursor_Rough SCROLL CURSOr
FOr Select a.ProductID,a.OrderID,a.FatherOrderID,b.PartName,b.PartPicNO ,c.IsDepot,c.IsWorkshop
From [技术网删除的零件] as a
inner join [tm_Part_info] as b On a.PartID=b.PartID
inner join [tm_PublicationMaterialInfo_Config] as c ON a.OrderID=c.OrderID
OPEN Cursor_Rough
FETCH FIRST From Cursor_Rough Into @ProductID,@OrderID,@FatherOrderID,@PartName,@PartPicNO,@IsDepot,@IsWorkshop
While @@FETCH_STATUS = 0
Begin
Delete From #tm_Product_tree
Insert Into #tm_Product_tree(OrderID)
Select OrderID
From [view_GetPartInfo]
Where ProductID=@ProductID And OrderID<>@OrderID
And OrderID In (Select OrderID From [技术网添加的零件])
And PartName=@PartName And PartPicNO=@PartPicNO
Update [tm_PublicationMaterialInfo_Config] Set IsDepot=@IsDepot
Where OrderID In (Select OrderID From #tm_Product_tree)
And (IsDepot Is Null Or IsDepot='')
Update [tm_PublicationMaterialInfo_Config] Set IsWorkshop=@IsWorkshop
Where OrderID In (Select OrderID From #tm_Product_tree)
And (IsWorkshop Is Null Or IsWorkshop='')
Insert Into [tm_PublicationMaterialInfo_Config](OrderID,IsDepot,IsWorkshop)
Select OrderID,@IsDepot,@IsWorkshop
From [tm_Product_tree]
Where OrderID In (Select OrderID From #tm_Product_tree)
And OrderID Not In (Select OrderID From [tm_PublicationMaterialInfo_Config])
IF exIsts(Select Top 1 OrderID From #tm_Product_tree)
Begin
Insert Into #del_Part(OrderID)
Values(@OrderID)
End
Print @OrderID
FETCH next From cursOr_Rough Into @ProductID,@OrderID,@FatherOrderID,@PartName,@PartPicNO,@IsDepot,@IsWorkshop
End
CLOSE cursOr_Rough
DEALLOCATE cursOr_Rough
Delete From [tm_Product_tree] Where OrderID in (Select OrderID From #del_Part)
--========================================================================================================
WHILE EXIsTS (Select Top 1 OrderID From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])))
Begin
Delete From [tm_Product_tree] Where OrderID in (
Select OrderID From [技术网删除的零件] Where OrderID Not In(Select FatherOrderID From [tm_Product_tree])
And OrderID Not in (Select OrderID From [tm_PublicationMaterialInfo_Config])
)
End
End --========================================================================================================
解决方案 »
- 请教一个复杂点的sql语句
- 怎样在存储过程中读取text、ntext、image类型字段的值并赋给相应变量?
- contains
- 请推荐一款hlp文件的编辑工具,谢谢!
- 请问: 我在sql server里做了一张表。 我想把我现有的文本里的数据导到数据库里。我应该怎么做?
- 高手请进(在线等!)
- 有关数据库备份
- 请问我使用backup备份的MSSQL数据库备份的文件(如abc.dat)为什么在硬盘上找不到?我如何将它copy到别的机器上再恢复?
- 那个高人解决了图片不显示,而显示红叉问题?
- 怎样用函数或存储过程校对SQL语句是否正确?
- SQL2000连接Java报错
- [求助]Sql企业管理器打不开数据表
Begin
Delete From [技术网剪贴的零件] Where ProductID_OLD=ProductID_New
Delete FROM [技术网剪贴的零件] WHERE OrderID_OLD In (select OrderID from [技术网删除的零件])
Delete FROM [技术网剪贴的零件] WHERE OrderID_OLD In (select OrderID from [技术网添加的零件])
WHILE EXISTS (Select OrderID_OLD From [技术网剪贴的零件] Where IsNull(OrderID_New,0)=0)
Begin
Select Top 1 @ProductID=ProductID_OLD,@FatherOrderID=FatherOrderID_OLD,@PartID=PartID_OLD,@OrderID=OrderID_Old,
@NewProductID=ProductID_New
From [技术网剪贴的零件] Where IsNull(OrderID_New,0)=0 Order By FatherOrderID_OLD,OrderID_Old
Insert Into [tm_Part_info](ProductID,PartName,PartPicNo,BaseOrderID,MaterialCoding,PartMaterialCode,PartMaterialTypeID,MaterialEditionStatus,MaterialEditionTime,PartProcessEditionStatus,MaterialEditionPersonID,MaterialTypeID,MaterialCodeSource)
Select @ProductID,PartName,PartPicNo,0,MaterialCoding,'',PartMaterialTypeID,MaterialEditionStatus,MaterialEditionTime,PartProcessEditionStatus,MaterialEditionPersonID,MaterialTypeID,MaterialCodeSource
From [tm_Part_info] Where PartID=@PartID
Set @NewPartID=@@IDENTITY
Insert Into [tm_Product_tree](ProductID,PartID,PartTypeid,PartCount,IsModify,PartIndex1,FatherOrderID,EditStatus,ProcessPath,Spec,comment,Material,Weight,PartFigSize,RoughFigSize,RoughCount,OrientNo,IsKeyPart,ReOrientNoFlag,WorkshopSection,PartBasicMetricUnitID,PartManufactory,PartIndustyDomain,PartEditFlag,PartSpareField1,PartSpareField2,PartSpareField3,PartSpareField4,ExtendedPropertyID1,ExtendedPropertyID2,StructureEditionStatus,StructureEditionTime,StructureEditionPersonID)
Select @ProductID,@NewPartID,PartTypeid,PartCount,IsModify,PartIndex1,FatherOrderID,EditStatus,ProcessPath,Spec,comment,Material,Weight,PartFigSize,RoughFigSize,RoughCount,OrientNo,IsKeyPart,ReOrientNoFlag,WorkshopSection,PartBasicMetricUnitID,PartManufactory,PartIndustyDomain,PartEditFlag,PartSpareField1,PartSpareField2,PartSpareField3,PartSpareField4,ExtendedPropertyID1,ExtendedPropertyID2,StructureEditionStatus,StructureEditionTime,StructureEditionPersonID
From [tm_Product_tree] Where OrderID=@OrderID
Set @NewOrderID=@@IDENTITY
Update [tm_Part_info] Set BaseOrderID=@NewOrderID,PartMaterialCode='#'+cast(PartID as nvarchar(50)) where PartID=@NewPartID
Insert Into [tm_BorrowPart_info](BaseProductID,BaseOrderID,BorrowProductID,BorrowOrderID,BorrowFlag)
Select BaseProductID,BaseOrderID,@ProductID,@NewOrderID,BorrowFlag
From [tm_BorrowPart_info]
Where BorrowOrderID=@OrderID Insert Into [tm_PublicationMaterialInfo_Config](OrderID,IsDepot,IsWorkshop,PartName,PartPicNo,Spec,Material,PartCount,length,TempletName,PartBasicMetricUnit,Comment)
Select @NewOrderID,IsDepot,IsWorkshop,PartName,PartPicNo,Spec,Material,PartCount,length,TempletName,PartBasicMetricUnit,Comment
From [tm_PublicationMaterialInfo_Config] Where OrderID=@OrderID IF EXISTS(Select RoughID From [tc_partpath_info] Where OrderID=@OrderID)
Begin
Set @RoughID=(Select top 1 RoughID From [tc_partpath_info] Where OrderID=@OrderID)
Insert Into [tc_partpath_info](PartPath,ProductID,OrderID,DefineType,RoughStatus,PartID)
Select '',@ProductID,@NewOrderID,DefineType,RoughStatus,@NewPartID
From [tc_partpath_info] Where RoughID=@RoughID
Set @NewRoughID=@@IDENTITY Insert Into [tc_rough_ration](RoughID,RoughcastID,PartSize,RoughSize,Cut,Collet,Gas,Length,RoughCount,UsingRate,Weight,MWeight,Ration,PWeight,PMWeight,PRation,ProcessPath,InputDate,EditStamp,PersonID,StatCount,PartIndex,IsPressed,Technology,Comment,PartSpec,IsSpare,ReportIndex,LengthCode,SectionCount,RoughSection,RationPartCountUnit,MWeightUnit,RationUnit,RoughSectionUnit,WeightUnit,PartCount)
Select @NewRoughID,RoughcastID,PartSize,RoughSize,Cut,Collet,Gas,Length,RoughCount,UsingRate,Weight,MWeight,Ration,PWeight,PMWeight,PRation,ProcessPath,InputDate,EditStamp,PersonID,StatCount,PartIndex,IsPressed,Technology,Comment,PartSpec,IsSpare,ReportIndex,LengthCode,SectionCount,RoughSection,RationPartCountUnit,MWeightUnit,RationUnit,RoughSectionUnit,WeightUnit,PartCount
From [tc_rough_ration] Where RoughID=@RoughID
Insert Into [tc_operation_record](RoughID,PropertyID,PropertyValue,RationUnit)
Select @NewRoughID,PropertyID,PropertyValue,RationUnit
From [tc_operation_record] Where RoughID=@RoughID
End
Update [技术网剪贴的零件] Set OrderID_New=@NewOrderID Where OrderID_OLD=@OrderID
Insert Into [技术网删除的零件](ProductID,OrderID,PartID,FatherOrderID)
Select ProductID_OLD,OrderID_New,PartID_New,FatherOrderID_OLD
From [技术网剪贴的零件] Where OrderID_New=@NewOrderID
Insert Into [技术网添加的零件](ProductID,OrderID)
Values( @NewProductID,@OrderID)
End
Delete From [技术网剪贴的零件]
End --========================================================================================================
WHILE EXIsTS (Select Top 1 OrderID From [tm_Product_tree] Where FatherOrderID>0 And FatherOrderID Not In (Select OrderID From [tm_Product_tree]))
Begin
Delete From [tm_Product_tree]
Where FatherOrderID>0 And FatherOrderID Not In (Select OrderID From [tm_Product_tree])
End
Delete From [tm_AmendBorrowPart_Info]
Delete From [tm_PublicationMaterialInfo]
Delete From [tm_Part_info] Where PartID not in (Select PartID From [tm_Product_tree])
Delete From [tm_Product_tree] Where PartID not in (Select PartID From [tm_Part_info])
Delete From [tm_BorrowPart_info] Where BorrowOrderID not in (Select OrderID From [tm_Product_tree])
Delete From [tm_BorrowPart_info] Where BaseOrderID>0 And BaseOrderID not in (Select OrderID From [tm_Product_tree])
Delete From [tm_PublicationMaterialInfo_Config] Where OrderID not in (Select OrderID From [tm_Product_tree])
Delete From [技术网删除的零件] Where OrderID not in (Select OrderID From [tm_Product_tree]) Delete From [tc_PartPath_info]
Where ProductID Not In(Select ProductID From [技术网删除的产品])
And OrderID Not In(Select OrderID From [技术网删除的零件])
And OrderID Not In(Select OrderID From [技术网剪贴的零件])
And RoughID Not In (Select RoughID From [DLCAPP2000].[dbo].[tc_PartPath_info])
And RoughID>142000 And dbo.CheckEditionStatus(RoughID,2)>0 Delete From [tc_PartPath_info]
Where ProductID Not In(Select ProductID From [技术网删除的产品])
And OrderID Not In(Select OrderID From [技术网删除的零件])
And OrderID Not In(Select OrderID From [技术网剪贴的零件])
And RoughID Not In (Select RoughID From [DLCAPP2000].[dbo].[tc_PartPath_info])
And RoughID<142000 Delete From [tc_partpath_info] Where OrderID not in (Select OrderID From [tm_Product_tree])
Delete From [tc_rough_ration] Where RoughID not in (Select RoughID From [tc_partpath_info])
Delete From [tc_operation_record] Where RoughID not in (Select RoughID From [tc_partpath_info]) --==========================================================================================================
Update a Set a.ProductID=b.ProductID
From tm_Product_tree as a inner join tm_Product_tree as b ON a.FatherOrderID=b.OrderID
Where a.FatherOrderID<>0 and a.ProductID<>b.ProductID
Update a set a.ProductID=b.ProductID
from tc_PartPath_info as a inner join tm_Product_tree as b ON a.OrderID=b.OrderID
where a.ProductID<>b.ProductID
Update a set a.PartID=b.PartID
from tc_PartPath_info as a inner join tm_Product_tree as b ON a.OrderID=b.OrderID
where a.PartID<>b.PartID
Update a set a.ProductID=b.ProductID
from 技术网删除的零件 as a inner join tm_Product_tree as b ON a.OrderID=b.OrderID
where a.ProductID<>b.ProductID
Update a set a.PartID=b.PartID
from 技术网删除的零件 as a inner join tm_Product_tree as b ON a.OrderID=b.OrderID
where a.PartID<>b.PartID
Update a set a.FatherOrderID=b.FatherOrderID
from 技术网删除的零件 as a inner join tm_Product_tree as b ON a.OrderID=b.OrderID
where a.FatherOrderID<>b.FatherOrderID
Update a set a.ProductID=b.ProductID
from 技术网添加的零件 as a inner join tm_Product_tree as b ON a.OrderID=b.OrderID
where a.ProductID<>b.ProductID --==========================================================================================================
Print '成功'
Set NOCOUNT OFF
End
游标能使用什么方式替换
还有什么方式能提高效率????