01. プロシージャの結果セットを持つ一時テーブルへのSELECT INTO

select * into #temp from sysobjects
select * from #temp

select * into #temp from sysobjects

if OBJECT_ID('tempdb... #temp') is not null
drop table #temp

select * into #temp from sysobjects 
select * from #temp

select * into #temp from sysobjects where 1=2
select * from #temp

create table test_getdate(c1 datetime) insert into test_getdate select GETDATE() select * from test_getdate

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

create table test_dbcc
(
TraceFlag varchar(100),
Status tinyint,
Global tinyint,
Session tinyint
)

insert into test_dbcc exec('DBCC TRACESTATUS')

select * from test_dbcc

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

exec sp_helptext sp_get_composite_job_info

INSERT INTO @xp_results 
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id

exec sp_configure 'show advanced options',1
RECONFIGURE
GO
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE
GO

select * into #JobInfo_S1
from openrowset('sqloledb', 'server=(local);trusted_connection=yes','exec msdb.dbo.sp_help_job')

select * from #JobInfo_S1

SELECT * INTO #JobInfo_S2
FROM OPENROWSET('SQLOLEDB','127.0.0.1';'sa';'sa_password','exec msdb.dbo.sp_help_job')

SELECT * FROM #JobInfo_S2

--dbcc cannot be run directly
SELECT a.* into #t
FROM OPENROWSET('SQLOLEDB','127.0.0.1';'sa';'sa_password',
'dbcc log(''master'',3)') AS a

-- can be adapted
SELECT a.* into #t
FROM OPENROWSET('SQLOLEDB','127.0.0.1';'sa';'sa_password',
'exec(''DBCC LOG(''''master'''',3)')') AS a