如何执行SELECT*INTO[temp table]FROM[存储过程]?不是FROM[Table]并且没有定义[temp Table]?

选择BusinessLine中的所有数据到tmpBusLine工作正常。

select *
into tmpBusLine
from BusinessLine

我也在尝试同样的方法,但使用返回数据的存储过程并不完全相同。

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

输出消息:

消息156,级别15,状态1,第2行关键字附近的语法不正确“exec”。

我读过几个创建与输出存储过程结构相同的临时表的示例,这很好,但最好不要提供任何列。


当前回答

我发现将数组/数据表传递到存储过程中,这可能会让您对如何解决问题有另一个想法。

该链接建议使用Image类型参数传递到存储过程中。然后在存储过程中,图像被转换为包含原始数据的表变量。

也许有一种方法可以用于临时表。

其他回答

在SQL Server 2005中,可以使用INSERT INTO。。。EXEC将存储过程的结果插入到表中。从MSDN的INSERT文档中(实际上是针对SQL Server 2000):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

在四处搜索之后,我找到了一种方法,可以在不使用OPENROWSET或OPENQUERY的情况下,使用存储过程结果定义的通用模式为任何存储过程动态创建临时表,特别是当您不是数据库管理员时。

Sql服务器有一个内置过程sp_describe_first_result_set,它可以为您提供任何过程结果集的模式。我根据这个过程的结果创建了一个模式表,并手动将所有字段设置为NULLABLE。

declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters 
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)

-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden  bit  NULL, column_ordinal   int  NULL, name sysname NULL, is_nullable   bit  NULL, system_type_id   int  NULL, system_type_name nvarchar(256) NULL,
max_length  smallint  NULL, precision   tinyint  NULL,  scale   tinyint  NULL,  collation_name  sysname NULL, user_type_id  int NULL, user_type_database    sysname NULL,
user_type_schema    sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name   nvarchar(4000),xml_collection_id    int NULL,xml_collection_database    sysname NULL,
xml_collection_schema   sysname NULL,xml_collection_name    sysname NULL,is_xml_document    bit  NULL,is_case_sensitive bit  NULL,is_fixed_length_clr_type  bit  NULL,
source_server   sysname NULL,source_database    sysname NULL,source_schema  sysname NULL,source_table   sysname NULL,source_column  sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key   bit NULL,is_updateable  bit NULL,is_computed_column bit NULL,is_sparse_column_set   bit NULL,ordinal_in_order_by_list   smallint NULL,
order_by_list_length    smallint NULL,order_by_is_descending    smallint NULL,tds_type_id   int  NULL,tds_length    int  NULL,tds_collation_id  int NULL,
tds_collation_sort_id   tinyint NULL)


-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0

-- Create a query to generate and populate a global temp table from above results
select 
@qry = 'Create table ##t(' +
stuff(  
    (select ',' + name + ' '+ system_type_name + ' NULL'
    from #d d For XML Path, TYPE)
    .value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')

insert into ##t 
Exec '+@procname+' ' + @param

Exec sp_executesql @qry

-- Use below global temp table to query the data as you may
select * from ##t

-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d 

在Sql Server版本-Microsoft Sql Server 2016(RTM)-13.0.1601.5(版本17134:)上开发和测试

您可以调整正在使用的SQL server版本的模式(如果需要)。

这是对您的问题稍作修改的答案。如果可以放弃对用户定义函数使用存储过程,则可以使用内联表值用户定义函数。这本质上是一个存储过程(将采用参数),它将表作为结果集返回;因此将与INTO语句一起放置。

这里有一篇关于它和其他用户定义函数的快速文章。如果仍然需要驱动存储过程,则可以用存储过程包装内联表值用户定义函数。存储过程在从内联表值用户定义函数调用select*时只传递参数。

例如,您可以使用一个内联表值用户定义函数来获取特定地区的客户列表:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

然后,您可以调用此函数来获取结果,例如:

SELECT * FROM CustomersbyRegion(1)

或执行SELECT INTO:

SELECT * INTO CustList FROM CustomersbyRegion(1)

如果仍然需要存储过程,则按如下方式包装函数:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

我认为这是获得理想结果的最“无黑客”的方法。它使用了现有的功能,因为它们的使用不会带来额外的复杂性。通过在存储过程中嵌套内联表值用户定义函数,您可以通过两种方式访问该功能。加实际SQL代码只有一个维护点。

已经建议使用OPENROWSET,但这不是OPENROWET函数的用途(摘自联机丛书):

包括所有连接信息访问远程数据所需的来自OLE DB数据源。这方法是访问表,并且是一个一次性临时连接方法和使用OLE访问远程数据数据库。更频繁的参考OLE DB数据源,使用链接服务器。

使用OPENROWSET可以完成任务,但在打开本地连接和编组数据时会产生一些额外的开销。它也可能不是所有情况下的选项,因为它需要临时查询权限,这会带来安全风险,因此可能不需要。此外,OPENROWSET方法将排除使用返回多个结果集的存储过程。在单个存储过程中包装多个内联表值用户定义函数可以实现这一点。

如果查询不包含参数,请使用OpenQuery,否则使用OpenRowset。

基本的事情是根据存储过程创建模式并插入到该表中。例如。:

DECLARE @abc TABLE(
                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
                )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc

最简单的解决方案:创建表#temp(…);插入#tempEXEC[sproc];

如果您不知道模式,那么可以执行以下操作。请注意,这种方法存在严重的安全风险。

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')