在开发过程中, 很多时候要把结果集存放到临时表中, 常用的方法有两种
一. SELECT INTO
1. 使用 select into 会自动生成临时表, 不需要事先创建
- select * into #temp from sysobjects
- 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 INTOEXEC 的嵌套调用, 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
来源: http://www.bubuko.com/infodetail-2536955.html