欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server 向临时表插入数据示例

程序员文章站 2023-12-01 22:56:40
复制代码 代码如下: 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秒就搞定。