SQL Server 向临时表插入数据示例
程序员文章站
2023-10-20 10:54:19
复制代码 代码如下: insert into #dirtyoldwipbom select top (100) percent dbo.wip_bom.model, dbo...
复制代码 代码如下:
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秒就搞定。