在開發(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元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
