SqlServer中把结果集放到到临时表的方法

在开发过程中，很多时候要把结果集存放到临时表中，常用的方法有两种。

 

　　一 . SELECT INTO

 

　　 1.  使用 select into 会自动生成临时表，不需要事先创建

 

　　 select * into #temp from sysobjects

 

　　 01.  把存储过程结果集 SELECT INTO 到临时表

 

　　 select * from #temp

 

　　 2.  如果当前会话中，已存在同名的临时表

 

　　 select * into #temp from sysobjects

 

　　再次运行，则会报错提示 : 数据库中已存在名为  '%1 ！ '  的对象。

 

　　 Msg 2714 ，  Level 16 ，  State 6 ，  Line 2

 

　　 There is already an object named '#temp' in the database.

 

　　在使用 select into 前，可以先做一下判断 :

 

　　 if OBJECT_ID （ 'tempdb..#temp' ）  is not null

 

　　 drop table #temp

 

　　 select * into #temp from sysobjects

 

　　 select * from #temp

 

　　 3.  利用 select into 生成一个空表

 

　　如果要生成一个空的表结构，不包含任何数据，可以给定一个恒不等式如下 :

 

　　 select * into #temp from sysobjects where 1 ＝ 2

 

　　 select * from #temp

 

　　二 . INSERT INTO

 

　　 1.  使用 insert into ，需要先手动创建临时表

 

　　 1.1  保存从 select 语句中返回的结果集

 

　　 create table test_getdate （ c1 datetime ）

 

　　 insert into test_getdate select GETDATE （）

 

　　 select * from test_getdate

 

　　 1.2  保存从存储过程返回的结果集

 

　　 create table #helpuser

 

　　 UserName nvarchar （ 128 ），

 

　　 RoleName nvarchar （ 128 ），

 

　　 LoginName nvarchar （ 128 ），

 

　　 DefDBName nvarchar （ 128 ），

 

　　 DefSchemaName nvarchar （ 128 ），

 

　　 UserID smallint ，

 

　　 SID smallint

 

　　）

 

　　 insert into #helpuser exec sp_helpuser

 

　　 select * from #helpuser

 

　　 1.3  保存从动态语句返回的结果集

 

　　 create table test_dbcc

 

　　 TraceFlag varchar （ 100 ），

 

　　 Status tinyint ，

 

　　 Global tinyint ，

 

　　 Session tinyint

 

　　）

 

　　 insert into test_dbcc exec （ 'DBCC TRACESTATUS' ）

 

　　 select * from test_dbcc

 

　　对于动态 SQL ，或者类似 DBCC 这种非常规的 SQL 语句，都可以通过这种方式来保存结果集。

 

　　 2.  不能嵌套使用 insert exec 语句

 

　　 2.1  下面这个例子，尝试保存 sp_help_job 的结果集到临时表，发生错误

 

　　 create table #JobInfo

 

　　 job_id uniqueidentifier ，

 

　　 originating_server nvarchar （ 128 ），

 

　　 name nvarchar （ 128 ），

 

　　 enabled tinyint ，

 

　　 description nvarchar （ 512 ），

 

　　 start_step_id int ，

 

　　 category nvarchar （ 128 ），

 

　　 owner nvarchar （ 128 ），

 

　　 notify_level_eventlog int ，

 

　　 notify_level_email int ，

 

　　 notify_level_netsend int ，

 

　　 notify_level_page int  ，

 

　　 notify_email_operator nvarchar （ 128 ），

 

　　 notify_netsend_operator nvarchar （ 128 ），

 

　　 notify_page_operator nvarchar （ 128 ），

 

　　 delete_level int ，

 

　　 date_created datetime ，

 

　　 date_modified datetime ，

 

　　 version_number int ，

 

　　 last_run_date int ，

 

　　 last_run_time int ，

 

　　 last_run_outcome int ，

 

　　 next_run_date int ，

 

　　 next_run_time int ，

 

　　 next_run_schedule_id int ，

 

　　 current_execution_status int ，

 

　　 current_execution_step nvarchar （ 128 ），

 

　　 current_retry_attempt int ，

 

　　 has_step int ，

 

　　 has_schedule int ，

 

　　 has_target int ，

 

　　 type int

 

　　）

 

　　 insert into #JobInfo exec msdb..sp_help_job

 

　　返回错误信息 :INSERT EXEC  语句不能嵌套。

 

　　 Msg 8164 ，  Level 16 ，  State 1 ，  Procedure sp_get_composite_job_info ，  Line 72

 

　　 An INSERT EXEC statement cannot be nested.

 

　　展开错误信息中的存储过程 :

 

　　 exec sp_helptext sp_get_composite_job_info

 

　　发现里面还有个 INSERT INTO…EXEC 的嵌套调用， SQL Server 在语法上不支持。

 

　　 INSERT INTO @xp_results

 

　　 EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs ，  @job_owner ， @job_id

 

　　 2.2  可以用分布式查询来避免这个问题， 这种写法在 INSIDE SQL Server 2005 中作者提到过

 

　　（ 1 ） 首先到打开服务器选项 Ad Hoc Distributed Queries

 

　　 exec sp_configure 'show advanced options' ， 1

 

　　 RECONFIGURE

 

　　 GO

 

　　 exec sp_configure 'Ad Hoc Distributed Queries' ， 1

 

　　 RECONFIGURE

 

　　 GO

 

　　（ 2 ） 通过 OPENROWSET 连接到本机，运行存储过程，取得结果集

 

　　使用 windows 认证

 

　　 select * into #JobInfo_S1

 

　　 from openrowset （ 'sqloledb' ，  'server ＝（ local ）； trusted_connection ＝ yes' ， 'exec msdb.dbo.sp_help_job' ）

 

　　 select * from #JobInfo_S1

 

　　使用 SQL Server 认证

 

　　 SELECT * INTO #JobInfo_S2

 

　　 FROM OPENROWSET （ 'SQLOLEDB' ， '127.0.0.1' ； 'sa' ； 'sa_password' ， 'exec msdb.dbo.sp_help_job' ）

 

　　 SELECT * FROM #JobInfo_S2

 

　　这样的写法，既免去了手动建表的麻烦，也可以避免 insert exec  无法嵌套的问题。几乎所有 SQL 语句都可以使用。

 

　　 --dbcc 不能直接运行

 

　　 SELECT a.* into #t

 

　　 FROM OPENROWSET （ 'SQLOLEDB' ， '127.0.0.1' ； 'sa' ； 'sa_password' ，

 

　　 'dbcc log （ ''master'' ， 3 ） ' ）  AS a

 

　　 -- 可以变通一下

 

　　 SELECT a.* into #t

 

　　 FROM OPENROWSET （ 'SQLOLEDB' ， '127.0.0.1' ； 'sa' ； 'sa_password' ，

 

　　 'exec （ ''DBCC LOG （ ''''master'''' ， 3 ） '' ） ' ）  AS a