简单例子:
CREATE TABLE #tmpArea(sz_code varchar(200), name varchar(200))
INSERT #tmpArea(sz_code, name) VALUES('110000', '北京市')
INSERT #tmpArea(sz_code, name) VALUES('120000', '天津市')
SELECT * FROM #tmpArea
DROP TABLE #tmpArea
使用变量代替临时表:
不用临时表,改用表变量, 如果数据量不大的情况下,用表变量速度上比较快.因为它是存储在内存里边的.
DECLARE @tmp TABLE(col1 int,col2 int)
INSERT INTO @tmp(col1, col2) VALUES('1', '11')
INSERT INTO @tmp(col1, col2) VALUES('2', '22')
SELECT * FROM @tmp
检测临时表是否存在:
IF (object_id('tempdb..#t') IS NOT NULL)
PRINT ''存在临时表#t'
ELSE
PRINT '不存在'
取得存储过程sp2的返回记录，直接select into到临时表：
select * into #t from OPENROWSET(
'SQLOLEDB','SERVER=servername;uid=sa;pwd=123;Database=testdb',
'SET FMTONLY OFF;set nocount on;exec sp2 参数') as a
select * from #t
drop table #t
--可以这样写:
select * into #t
from openrowset('sqloledb','Trusted_Connection=yes','SET FMTONLY OFF;set nocount on;exec 库名.dbo.sp2 参数')
--或者这样写吧,就不用在调用存储过程中指定库名,当然,连接字符串中就要字符串了
select * into #t
from openrowset('sqloledb','Trusted_Connection=yes;Database=testdb','SET FMTONLY OFF;set nocount on;exec sp2 参数')
MS SQL 帮助: CREATE TABLE
