在開發(fā)過(guò)程中，很多時(shí)候要把結(jié)果集存放到臨時(shí)表中，常用的方法有兩種。
一. SELECT INTO
1. 使用select into會(huì)自動(dòng)生成臨時(shí)表，不需要事先創(chuàng)建
select
*
into
#
temp
from
sysobjects
select
*
from
#
temp
?
2. 如果當(dāng)前會(huì)話中，已存在同名的臨時(shí)表
select
*
into
#
temp
from
sysobjects
?
再次運(yùn)行，則會(huì)報(bào)錯(cuò)提示：數(shù)據(jù)庫(kù)中已存在名為 '%1!' 的對(duì)象。
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生成一個(gè)空表
如果要生成一個(gè)空的表結(jié)構(gòu)，不包含任何數(shù)據(jù)，可以給定一個(gè)恒不等式如下：
select
*
into
#
temp
from
sysobjects
where
1
=
2
select
*
from
#
temp
?
?
二. INSERT INTO
1. 使用insert into，需要先手動(dòng)創(chuàng)建臨時(shí)表
1.1 保存從select語(yǔ)句中返回的結(jié)果集
create
table
test_getdate(c1
datetime
)
insert
into
test_getdate
select
GETDATE
()
select
*
from
test_getdate
?
1.2 保存從存儲(chǔ)過(guò)程返回的結(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 保存從動(dòng)態(tài)語(yǔ)句返回的結(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
?
對(duì)于動(dòng)態(tài)SQL，或者類似DBCC這種非常規(guī)的SQL語(yǔ)句，都可以通過(guò)這種方式來(lái)保存結(jié)果集。
?
2. 不能嵌套使用insert exec語(yǔ)句
2.1 下面這個(gè)例子，嘗試保存sp_help_job的結(jié)果集到臨時(shí)表，發(fā)生錯(cuò)誤
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
?
返回錯(cuò)誤信息：INSERT EXEC 語(yǔ)句不能嵌套。
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
展開錯(cuò)誤信息中的存儲(chǔ)過(guò)程：
exec
sp_helptext sp_get_composite_job_info
?
發(fā)現(xiàn)里面還有個(gè)INSERT INTO…EXEC的嵌套調(diào)用，SQL Server在語(yǔ)法上不支持。
INSERT
INTO
@xp_results
EXECUTE
master.dbo.xp_sqlagent_enum_jobs
@can_see_all_running_jobs
,
@job_owner
,
@job_id
?
?
2.2 可以用分布式查詢來(lái)避免這個(gè)問(wèn)題，
這種寫法在INSIDE SQL Server 2005中作者提到過(guò)
(1) 首先到打開服務(wù)器選項(xiàng)Ad Hoc Distributed Queries
exec
sp_configure
'
show advanced options
'
,
1
RECONFIGURE
GO
exec
sp_configure
'
Ad Hoc Distributed Queries
'
,
1
RECONFIGURE
GO
?
(2) 通過(guò)OPENROWSET連接到本機(jī)，運(yùn)行存儲(chǔ)過(guò)程，取得結(jié)果集
使用windows認(rèn)證
select
*
into
#JobInfo_S1
from
openrowset
(
'
sqloledb
'
,
'
server=(local);trusted_connection=yes
'
,
'
exec msdb.dbo.sp_help_job
'
)
select
*
from
#JobInfo_S1
?
使用SQL Server認(rèn)證
SELECT
*
INTO
#JobInfo_S2
FROM
OPENROWSET
(
'
SQLOLEDB
'
,
'
127.0.0.1
'
;
'
sa
'
;
'
sa_password
'
,
'
exec msdb.dbo.sp_help_job
'
)
SELECT
*
FROM
#JobInfo_S2
?
這樣的寫法，既免去了手動(dòng)建表的麻煩，也可以避免insert exec 無(wú)法嵌套的問(wèn)題。幾乎所有SQL語(yǔ)句都可以使用。
--
dbcc不能直接運(yùn)行
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號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
