频道导航

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

2019-01-04 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了SQL Server 向临时表插入数据示例，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

<div class="codetitle"><a style="CURSOR: pointer" data="35645" class="copybut" id="copybut35645" onclick="doCopy('code35645')"> 代码如下:

<div class="codebody" id="code35645">
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))

这样就可以成功。
<div class="codetitle"><a style="CURSOR: pointer" data="27359" class="copybut" id="copybut27359" onclick="doCopy('code27359')"> 代码如下:

<div class="codebody" id="code27359">
但SELECT TOP (100) PERCENT dbo.WIP_BOM.Model,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秒就搞定。

原文链接：https://www.f2er.com/mssql/63262.html

临时表插入数据