存储过程中临时表的使用

下面是在存储过程中临时表的使用

create PROCEDURE insertGoodsPlan
(
	@billCode varchar(50)
)
AS
BEGIN
  declare @goodsId nvarchar(30)
  declare @gcount numeric --需求数量
  declare @KHWLID nvarchar(30)

begin 

select @KHWLID=WLID from SALEORDER where BILLCODE=@billCode

select a.goodsid,a.UNITID,sum(GCOUNT) as XQCount,
       (select sum(ENCount) as ENCount from (
                     select sum(ia.STCount+ia.CICount-ia.COCount) as ENCount from BASE_STGOODS as ia where ia.GOODSID=a.goodsId and ia.WLID=@KHWLID
                     union 
                     select sum(ia.STCount+ia.CICount-ia.COCount) as ENCount from BASE_STGOODS as ia where ia.GOODSID=a.goodsId and (ia.WLID='' or ia.WLID is null)
                     ) as ib
       ) as ENCount,
       (select sum(GCOUNT) as GCOUNT from PRODUCE_TASKSDETAIL where GOODSID=a.goodsid and ISSubtract='0') as GCOUNT
       into #a
from PRODUCE_TASKSDETAIL as a
where SALEORDER_BILLCODE=@billCode group by a.goodsid,a.UNITID



--    向物料需求计划表中插入相应的数据
		insert into PRODUCE_PLANGOODS(SALEORDER_BILLCODE,GOODSID,KHWLID,UNITID,XQCount,ENCount,KYCount)
              select @billCode,goodsId,@KHWLID,UNITID,gCount,ENCount,ENCount-GCOUNT from #a

 drop table #a
end
end

 临时表

 

1.可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。

2.本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。

SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

drop table #MyTempTable

select * into #a from tableName  --把表结构和数据都添加到了临时表中

insert into PRODUCE_PLANGOODS
(
SALEORDER_BILLCODE,
GOODSID,
KHWLID,
UNITID,
XQCount,
ENCount,
KYCount)
 select @billCode,goodsId,@KHWLID,UNITID,gCount,ENCount,
ENCount-GCOUNT from #a

 --这样就把临时表中的数据都添加到了表中