复制代码 代码如下:
INSERT INTO #DirtyOldWIPBOM SELECt TOP (100) PERCENT dbo.WIP_BOM.Model, dbo.WIP_BOM.PartNumber,WIP_BOM.WIP
FROM dbo.WIP_BOM left OUTER JOIN dbo.BOM_CHINA ON LTRIm(dbo.WIP_BOM.Model) = LTRIm(dbo.BOM_CHINA.Model) AND LTRIm(dbo.WIP_BOM.PartNumber) = LTRIm(dbo.BOM_CHINA.PartNumber) AND dbo.WIP_BOM.Qty = dbo.BOM_CHINA.Qty AND BOM_CHINA.WIP=
WIP_BOM.WIP WHERe (dbo.BOM_CHINA.Model IS NULL) AND EXISTS(SELECt * FROM dbo.BOM_CHINA WHERe WIP = WIP_BOM.WIP AND LTRIm(Model) = LTRIm(WIP_BOM.Model))
这样就可以成功。
复制代码 代码如下:
但SELECt TOP (100) PERCENT dbo.WIP_BOM.Model, dbo.WIP_BOM.PartNumber,WIP_BOM.WIP into #DirtyOldWIPBOM
FROM dbo.WIP_BOM left OUTER JOIN dbo.BOM_CHINA ON LTRIm(dbo.WIP_BOM.Model) = LTRIm(dbo.BOM_CHINA.Model) AND LTRIm(dbo.WIP_BOM.PartNumber) = LTRIm(dbo.BOM_CHINA.PartNumber) AND dbo.WIP_BOM.Qty = dbo.BOM_CHINA.Qty AND BOM_CHINA.WIP=
WIP_BOM.WIP WHERe (dbo.BOM_CHINA.Model IS NULL) AND EXISTS(SELECt * FROM dbo.BOM_CHINA WHERe WIP = WIP_BOM.WIP AND LTRIm(Model) = LTRIm(WIP_BOM.Model))
会提示error "There is already an object named '#DirtyOldWIPBOM' in the database"
为什么？
[笔记]:千万不要用游标，用临时表和表变量代替游标会极大的提高性能，今天用游标处理700行数据用了2小时，用临时表不到1秒就搞定。
