如何执行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”。

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


当前回答

如果要在不首先声明临时表的情况下执行此操作,可以尝试创建用户定义函数而不是存储过程,并使该用户定义函数返回表。或者,如果要使用存储过程,请尝试以下操作:

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

其他回答

如果存储过程的结果表太复杂,无法手动键入“createtable”语句,并且不能使用OPENQUERY或OPENROWSET,则可以使用sp_help为您生成列和数据类型列表。一旦您有了列列表,就只需要将其格式化以满足您的需要。

步骤1:将“into#temp”添加到输出查询中(例如“select[…]into#TEMPfrom[…]”)。

最简单的方法是直接在proc中编辑输出查询。如果无法更改存储的proc,可以将内容复制到新的查询窗口中,并在其中修改查询。

步骤2:对临时表运行sp_help。(例如“exec tempdb..sp_help#temp”)

创建临时表后,对临时表运行sp_help以获取列和数据类型的列表,包括varchar字段的大小。

步骤3:将数据列和类型复制到createtable语句中

我有一个Excel工作表,用于将sp_help的输出格式化为“createtable”语句。您不需要任何花哨的东西,只需复制并粘贴到SQL编辑器中即可。使用列名、大小和类型构造“Createtable#x[…]”或“declare@xtable[…]“语句,您可以使用该语句插入存储过程的结果。

步骤4:插入新创建的表

现在,您将得到一个与本主题中描述的其他解决方案类似的查询。

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

此技术也可用于将临时表(#temp)转换为表变量(@temp)。虽然这可能比自己编写createtable语句要简单得多,但它可以防止大型进程中出现诸如拼写错误和数据类型不匹配等手动错误。调试拼写错误可能比一开始编写查询要花费更多的时间。

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

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

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

这是对您的问题稍作修改的答案。如果可以放弃对用户定义函数使用存储过程,则可以使用内联表值用户定义函数。这本质上是一个存储过程(将采用参数),它将表作为结果集返回;因此将与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方法将排除使用返回多个结果集的存储过程。在单个存储过程中包装多个内联表值用户定义函数可以实现这一点。

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

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

嗯,您确实需要创建一个临时表,但它不需要有正确的模式。。。。我创建了一个存储过程,它修改了现有的临时表,使其具有正确的数据类型和顺序(删除所有现有列,添加新列)所需的列:

GO
create procedure #TempTableForSP(@tableId int, @procedureId int)  
as   
begin  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
                                        );    
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename(c.name) + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
                                  )    
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  
end         
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

请注意,如果sys.dm_exec_descript_first_result_set_for_object无法确定存储过程的结果(例如,如果它使用临时表),则这将不起作用。