临时表

转自：http://www.cnblogs.com/jeffwongishandsome/archive/2009/08/05/1526466.html

1、创建方法:
方法一：
create table TempTableName
或
select [字段1,字段2,...,] into TempTableName from table 
方法二：
create table tempdb.MyTempTable(Tid int)
说明：
（1）、临时表其实是放在数据库tempdb里的一个用户表；
（2）、TempTableName必须带“#”，“#"可以是一个或者两个，以#(局部)或##(全局)开头的表,这种表在会话期间存在,会话结束则自动删除;
（3）、如果创建时不以#或##开头,而用tempdb.TempTable来命名它,则该表可在数据库重启前一直存在。
2、手动删除
drop table TempTableName
说明：

 DROP  TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：     
（1）、当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表；
（2）、所有其它本地临时表在当前会话结束时自动除去；
（3）、全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个Transact-SQL语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的Transact-SQL语句完成后，将自动除去此表。
3、示例代码
（1）创建

use testdb

--创建局部临时表 
create table #tmpStudent(Tid int,Name varchar(50),Age int)

insert into #tmpStudent values('xiaowang',25)

select * from #tmpStudent

--创建局部临时表 另一种写法
select *  into #tmpStudent from student

select * from #tmpStudent

第二种创建方法：

 

create table tempdb.MyTempTable(Tid int) --有对应权限才可以这么写

（2）删除

drop table #tmpStudent

 

自己的例子:

ALTER PROC usp_BatchExecSql
AS
BEGIN
SELECT IDENTITY(INT,1,1) AS id,* INTO #temp FROM dbo.BatchExecSql --把表数据插入临时表中
DECLARE @index INT=2;
DECLARE @count INT;
DECLARE @sqlStr VARCHAR;
DECLARE @_Column1 VARCHAR;
DECLARE @_Column2 VARCHAR;
DECLARE @_Column3 VARCHAR;
DECLARE @_Column4 VARCHAR;
DECLARE @_Column5 VARCHAR;
DECLARE @_Column6 VARCHAR;
SET @count=(SELECT COUNT(*) FROM #temp);
WHILE @index<=@count --循环条件
BEGIN
SELECT @_Column1=Column1,@_Column2=Column2,@_Column3=Column3,@_Column4=Column4,@_Column5=Column5,@_Column6=Column6 FROM #temp WHERE ID=@index;
IF @_Column1 IS NOT NULL
BEGIN
IF EXISTS(SELECT ID FROM dbo.AP_INVOICES WHERE STATUS='Ready to Post' AND BookingCode=(SELECT ID FROM dbo.AP_DISTRIBUTOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column1))) AND AP_VENDOR_ID in (SELECT VENDORID FROM dbo.AP_DISTRIBUTOR_VENDOR WHERE VENDORID IN(SELECT ID FROM dbo.AP_VENDOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column4))) AND DISTRIBUTORID=(SELECT ID FROM dbo.AP_DISTRIBUTOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column1))) GROUP BY VENDORID) AND RTRIM(LTRIM(INVOICE_NUM))=RTRIM(LTRIM(@_Column5)) AND RTRIM(LTRIM(TOTAL))=CAST(RTRIM(LTRIM(@_Column6)) AS MONEY)) BEGIN UPDATE dbo.AP_INVOICES_CSTM SET BATCH_NUM_C=RTRIM(LTRIM(@_Column2)),BATCH_DATE_C=CAST(RTRIM(LTRIM(@_Column3)) AS DATETIME) WHERE ID_C in (SELECT ID FROM dbo.AP_INVOICES WHERE STATUS='Ready to Post' AND BookingCode=(SELECT ID FROM dbo.AP_DISTRIBUTOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column1))) AND AP_VENDOR_ID in (SELECT VENDORID FROM dbo.AP_DISTRIBUTOR_VENDOR WHERE VENDORID IN(SELECT ID FROM dbo.AP_VENDOR WHERE DELETED=0 AND CODE=RTRIM(LTRIM(@_Column4))) AND DISTRIBUTORID=(SELECT ID FROM dbo.AP_DISTRIBUTOR WHERE DELETED=0 AND CODE=RTRIM(LTRIM(@_Column1))) GROUP BY VENDORID) AND RTRIM(LTRIM(INVOICE_NUM))=RTRIM(LTRIM(@_Column5)) AND RTRIM(LTRIM(TOTAL))=CAST(RTRIM(LTRIM(@_Column6)) AS MONEY)); UPDATE dbo.AP_INVOICES SET STATUS='Closed',DATE_MODIFIED=GETDATE() WHERE ID in (SELECT ID FROM dbo.AP_INVOICES WHERE STATUS='Ready to Post' AND BookingCode=(SELECT ID FROM dbo.AP_DISTRIBUTOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column1))) AND AP_VENDOR_ID in (SELECT VENDORID FROM dbo.AP_DISTRIBUTOR_VENDOR WHERE VENDORID IN(SELECT ID FROM dbo.AP_VENDOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column4))) AND DISTRIBUTORID=(SELECT ID FROM dbo.AP_DISTRIBUTOR WHERE DELETED=0 AND RTRIM(LTRIM(CODE))=RTRIM(LTRIM(@_Column1))) GROUP BY VENDORID) AND RTRIM(LTRIM(INVOICE_NUM))=RTRIM(LTRIM(@_Column5)) AND RTRIM(LTRIM(TOTAL))=CAST(RTRIM(LTRIM(@_Column6)) AS MONEY)) END;
END
SET @index = @index+1;
END
DROP TABLE #temp
END