01. 把存储过程结果集SELECT INTO到临时表

在开发过程中,很多时候要把结果集存放到临时表中,常用的方法有两种。

一. 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

 

备注:(更新:2018-09-20)

(1) 通过select into复制表默认会保留identity列属性, 从linked server复制表则不会;

--server1, database1
create table test_identity(id int identity, value int)
insert into test_identity values(100)

--server2, database2
select * into temp
from sever1.database1.dbo.test_identity

select object_name(object_id) as table_name, name, is_identity,* 
from sys.columns 
where object_id=object_id('temp') 
/*
table_name    name    is_identity
temp    id    0
*/

(2) 列的是否为null属性默认直接复制,如果显式给定列值,则目标表的列属性不允许为null;

--principal_id列定义可为空
exec sp_help 'sys.objects'

drop table if exists test_null01;
drop table if exists test_null02;

select principal_id into test_null01 from sys.objects
select isnull(principal_id,0) as principal_id into test_null02 from sys.objects

select name, is_nullable,* from sys.columns where object_id = object_id('test_null01')
--name    is_nullable
--principal_id    1
select name, is_nullable,* from sys.columns where object_id = object_id('test_null02')
--name    is_nullable
--principal_id    0

select isnull(null,'') c1 into test_null_01
select '' c1 into test_null_02
select 1 c1 into test_null_03

exec sp_columns test_null_01
exec sp_columns test_null_02
exec sp_columns test_null_03
--NULLABLE
--0

(3) 如果显式给定列值为null,或者join后列值全部为null, 目标表中该列的数据类型默认为int,除非用CAST/CONVERT显式指定null列的数据类型;

--if get only null value after join, select into will use int for null-value columns as well
select null as data_type into test_data_type;
exec sp_columns test_data_type

(4) SELECT… INTO… 除了复制identity属性外,仅复制数据,所以原表上的约束/索引/压缩选项等都不会被复制,所以从columnstore的表拉数据出来,会发现表变大了很多了,因为columnstore默认压缩数据,这种场景可考虑使用insert into… with(tablock) select… 结合610跟踪标记来替代SELECT… INTO;

(5) 从SQL SERVER 2014起,SELECT …INTO…的插入操作,执行计划显示为并行化操作符,也即插入操作不再是单线程;

 

二. 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 INTO…EXEC的嵌套调用,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 

 

后续的SQL SERVER版本中,这种写法有限制 (更新:2018-09-19)

1. 在SQL SERVER 2008 R2下测试,问题如下:

--sp_help_job没问题
SELECT * FROM
OPENROWSET ('SQLOLEDB','Server=.\SQLEXPRESS;Trusted_Connection=yes','EXEC msdb.dbo.sp_help_job')

--随手写了几个sp_who2, xp_fixeddrives, sp_helpdb,都失败了
select * from 
OPENROWSET('SQLOLEDB','Server=.\SQLEXPRESS;TRUSTED_CONNECTION=YES;','exec sp_who2')
/*
Msg 7357, Level 16, State 2, Line 2
Cannot process the object "exec sp_who2". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
*/

select * from 
OPENROWSET('SQLOLEDB','Server=.\SQLEXPRESS;TRUSTED_CONNECTION=YES;','exec xp_fixeddrives')
/*
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec xp_fixeddrives". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
*/

select * from 
OPENROWSET('SQLOLEDB','Server=.\SQLEXPRESS;TRUSTED_CONNECTION=YES;','exec sp_helpdb')
/*
Msg 208, Level 16, State 1, Procedure sp_helpdb, Line 51
Invalid object name '#spdbdesc'.
*/

2.在SQL SERVER 2012, 2014, 2016下测试,问题如下:

--sp_help_job也失败了
SELECT * FROM
OPENROWSET ('SQLOLEDB','Server=.\MSSQL2016;Trusted_Connection=yes','EXEC msdb.dbo.sp_help_job')
/*
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure 'sp_is_sqlagent_starting' invokes an extended stored procedure.*/


--sp_who2, xp_fixeddrives, sp_helpdb,错误也都相对统一了
select * from 
OPENROWSET('SQLOLEDB','Server=.\MSSQL2016;TRUSTED_CONNECTION=YES;','exec sp_who2')
/*
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'delete #tb1_sysprocesses
         where   lower(status)  = 'sleeping'
         and     upper(cmd)    in (' in procedure 'sp_who2' uses a temp table.
*/

select * from 
OPENROWSET('SQLOLEDB','Server=.\MSSQL2016;TRUSTED_CONNECTION=YES;','exec xp_fixeddrives')
/*
Msg 11519, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'exec xp_fixeddrives' invokes an extended stored procedure.
*/

select * from 
OPENROWSET('SQLOLEDB','Server=.\MSSQL2016;TRUSTED_CONNECTION=YES;','exec sp_helpdb')
/*
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
        select name, isnull(suser_sname(s' in procedure 'sp_helpdb' uses a temp table.
*/

可以看出是因为不能确定所返回结果集的meta信息导致的:

EXEC sp_describe_first_result_set @tsql = N'exec msdb.dbo.sp_help_job'
GO
/*
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure 'sp_is_sqlagent_starting' invokes an extended stored procedure.
*/

变通的解决办法:自定义SP对需要调用的系统SP包装一次,用WITH RESULT SETS返回固定的结果集,从而避免这个错误;

注意WITH RESULT SETS选项从SQL SERVER 2012起开始支持,实例如下:

USE MSDB
GO

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_job_with_results')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_job_with_results
go
CREATE PROCEDURE sp_help_job_with_results
  @job_id                     UNIQUEIDENTIFIER = NULL,  
  @job_name                   SYSNAME          = NULL,  
  @job_aspect                 VARCHAR(9)       = NULL,  
  @job_type                   VARCHAR(12)      = NULL,
  @owner_login_name           SYSNAME          = NULL,
  @subsystem                  NVARCHAR(40)     = NULL,
  @category_name              SYSNAME          = NULL,
  @enabled                    TINYINT          = NULL,
  @execution_status           INT              = NULL,
  @date_comparator            CHAR(1)          = NULL,
  @date_created               DATETIME         = NULL,
  @date_last_modified         DATETIME         = NULL,
  @description                NVARCHAR(512)    = NULL
AS
BEGIN
    -- If job_id or job_name were not specified there will be only one resultset
    IF (@job_id IS NULL AND @job_name IS NULL)
    BEGIN
        EXEC sp_help_job @job_id,  
            @job_name,
            @job_aspect,
            @job_type,
            @owner_login_name,
            @subsystem,
            @category_name,
            @enabled,
            @execution_status,
            @date_comparator,
            @date_created,
            @date_last_modified,
            @description
            WITH RESULT SETS
            ( 
             (
                job_id                        UNIQUEIDENTIFIER, 
                originating_server            NVARCHAR(30), 
                name                        SYSNAME, 
                [enabled]                    TINYINT, 
                [description]                NVARCHAR(512), 
                start_step_id                INT, 
                category                    SYSNAME, 
                [owner]                        SYSNAME, 
                notify_level_eventlog        INT, 
                notify_level_email            INT, 
                notify_level_netsend        INT, 
                notify_level_page            INT, 
                notify_email_operator        SYSNAME, 
                notify_netsend_operator        SYSNAME, 
                notify_page_operator        SYSNAME, 
                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        SYSNAME, 
                current_retry_attempt        INT, 
                has_step                    INT, 
                has_schedule                INT, 
                has_target                    INT, 
                [type]                        INT 
             )
            )
    END
    ELSE
    BEGIN
        -- If job_id or job_name is not null, there will be multiple resultsets
        EXEC sp_help_job @job_id,  
            @job_name,
            @job_aspect,
            @job_type,
            @owner_login_name,
            @subsystem,
            @category_name,
            @enabled,
            @execution_status,
            @date_comparator,
            @date_created,
            @date_last_modified,
            @description
            WITH RESULT SETS
            ( 
             (
                job_id                        UNIQUEIDENTIFIER, 
                originating_server            NVARCHAR(30), 
                name                        SYSNAME, 
                [enabled]                    TINYINT, 
                [description]                NVARCHAR(512), 
                start_step_id                INT, 
                category                    SYSNAME, 
                [owner]                        SYSNAME, 
                notify_level_eventlog        INT, 
                notify_level_email            INT, 
                notify_level_netsend        INT, 
                notify_level_page            INT, 
                notify_email_operator        SYSNAME, 
                notify_netsend_operator        SYSNAME, 
                notify_page_operator        SYSNAME, 
                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        SYSNAME, 
                current_retry_attempt        INT, 
                has_step                    INT, 
                has_schedule                INT, 
                has_target                    INT, 
                [type]                        INT 
             ),
             (
                step_id                        INT,
                step_name                    SYSNAME,
                subsystem                    NVARCHAR(40)    ,
                command                        NVARCHAR(max)    ,
                flags                        NVARCHAR(4000),
                cmdexec_success_code        INT,
                on_success_action            NVARCHAR(4000),
                on_success_step_id            INT,
                on_fail_action                NVARCHAR(4000),
                on_fail_step_id                INT,
                [server]                    SYSNAME,      
                database_name                SYSNAME,
                database_user_name            SYSNAME,
                retry_attempts                INT,
                retry_interval                INT,
                os_run_priority                NVARCHAR(4000),  
                output_file_name            NVARCHAR(200),
                last_run_outcome            INT,
                last_run_duration            INT,
                last_run_retries            INT,
                last_run_date                INT,
                last_run_time                INT,
                proxy_id                    INT
            ),
            (
                schedule_id                    INT,
                schedule_name                SYSNAME,
                [enabled]                    INT,
                freq_type                    INT,
                freq_interval                INT,
                freq_subday_type            INT,
                freq_subday_interval        INT,
                freq_relative_interval        INT,
                freq_recurrence_factor        INT,
                active_start_date            INT,
                active_end_date                INT,
                active_start_time            INT,
                active_end_time                INT,
                date_created                DATETIME,
                schedule_description        NVARCHAR(4000) ,
                next_run_date                INT,
                next_run_time                INT,
                schedule_uid                UNIQUEIDENTIFIER,
                job_count                    INT
            ),
            (
                server_id                    INT,
                server_name                    NVARCHAR(30),
                enlist_date                    DATETIME,
                last_poll_date                DATETIME,
                last_run_date                INT,
                last_run_time                INT,
                last_run_duration            INT,
                last_run_outcome            TINYINT,
                last_outcome_message        NVARCHAR(1024) 
            )
            )
    END
END
GO
sp_help_job_with_results
IF (EXISTS (SELECT *
            FROM sysobjects
            WHERE (name = 'sp_fixeddrives')
              AND (type = 'P')))
DROP PROCEDURE sp_fixeddrives
GO

CREATE PROCEDURE sp_fixeddrives
AS
BEGIN
        EXEC xp_fixeddrives
        WITH RESULT SETS
            ( 
             (
                drive   varchar(10),
                [MB Free]  varchar(100)
             )
            )
END
sp_fixeddrives

调用封装过的SP:

SET FMTONLY OFF
EXEC sp_describe_first_result_set @tsql = N'exec msdb.dbo.sp_help_job_with_results'
GO
EXEC sp_describe_first_result_set @tsql = N'exec sp_fixeddrives'
GO

--直接调用sp_help_job失败
SELECT * FROM
OPENROWSET('SQLOLEDB','Server=.\MSSQL2016;TRUSTED_CONNECTION=YES;','exec msdb.dbo.sp_help_job')
--封装为sp_help_job_with_results后调用成功
SELECT * FROM
OPENROWSET ('SQLOLEDB','Server=.\MSSQL2016;Trusted_Connection=yes','EXEC msdb.dbo.sp_help_job_with_results')


--直接调用xp_fixeddrives失败
SELECT * FROM
OPENROWSET('SQLOLEDB','Server=.\MSSQL2016;TRUSTED_CONNECTION=YES;','exec xp_fixeddrives')

--封装为sp_fixeddrives后调用成功
SELECT * FROM
OPENROWSET('SQLOLEDB','Server=.\MSSQL2016;TRUSTED_CONNECTION=YES;','exec sp_fixeddrives')

 


posted @ 2013-07-29 11:25 张骞 阅读(...) 评论(...) 编辑 收藏