在開發(fā)過程中,很多時候要把結(jié)果集存放到臨時表中,常用的方法有兩種。
一. SELECT INTO
1. 使用select into會自動生成臨時表,不需要事先創(chuàng)建
select * into # temp from sysobjects select * from # temp
?
2. 如果當(dāng)前會話中,已存在同名的臨時表
select * into # temp from sysobjects
?
再次運行,則會報錯提示:數(shù)據(jù)庫中已存在名為 '%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生成一個空表
如果要生成一個空的表結(jié)構(gòu),不包含任何數(shù)據(jù),可以給定一個恒不等式如下:
select * into # temp from sysobjects where 1 = 2 select * from # temp
?
?
二. INSERT INTO
1. 使用insert into,需要先手動創(chuàng)建臨時表
1.1 保存從select語句中返回的結(jié)果集
create table test_getdate(c1 datetime )
insert into test_getdate select GETDATE ()
select * from test_getdate
?
1.2 保存從存儲過程返回的結(jié)果集
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 保存從動態(tài)語句返回的結(jié)果集
create table test_dbcc ( TraceFlag varchar ( 100 ), Status tinyint , Global tinyint , Session tinyint ) insert into test_dbcc exec ( ' DBCC TRACESTATUS ' ) select * from test_dbcc
?
對于動態(tài)SQL,或者類似DBCC這種非常規(guī)的SQL語句,都可以通過這種方式來保存結(jié)果集。
?
2. 不能嵌套使用insert exec語句
2.1 下面這個例子,嘗試保存sp_help_job的結(jié)果集到臨時表,發(fā)生錯誤
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
?
發(fā)現(xiàn)里面還有個INSERT INTO…EXEC的嵌套調(diào)用,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) 首先到打開服務(wù)器選項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連接到本機,運行存儲過程,取得結(jié)果集
使用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
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
