将存储过程的结果插入临时表

如何SELECT * INTO [temp table] FROM [stored procedure]进行SELECT * INTO [temp table] FROM [stored procedure] ?不是FROM [Table] ,也没有定义[temp table]

BusinessLine Select所有数据到tmpBusLine可以正常工作。

select *
into tmpBusLine
from BusinessLine

我正在尝试相同的方法,但是使用返回数据的stored procedure并不完全相同。

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

输出信息:

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

我已经阅读了几个创建与输出存储过程具有相同结构的临时表的示例,该示例工作正常,但最好不要提供任何列。

答案

您可以为此使用OPENROWSET 。看一看。我还包含了 sp_configure 代码,以启用临时分布式查询(如果尚未启用)。

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

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

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

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

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

这是对您的问题的稍作修改的答案。如果可以放弃对用户定义的函数使用存储过程,则可以使用内联表值用户定义的函数。本质上,这是一个存储过程(将带有参数),该存储过程返回一个表作为结果集。因此可以很好地放置 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,但这不是 OPENROWSET 函数打算用于的目的(摘自 Online Books):

包括从 OLE DB 数据源访问远程数据所需的所有连接信息。此方法是访问链接服务器中的表的替代方法,并且是使用 OLE DB 连接和访问远程数据的一次性临时方法。要更频繁地引用 OLE DB 数据源,请改用链接服务器。

使用 OPENROWSET 可以完成工作,但会增加一些打开本地连接和编组数据的开销。由于它需要临时查询权限,因此可能并非在所有情况下都是一个选择,这会带来安全风险,因此可能不希望使用。同样,OPENROWSET 方法将排除使用存储过程返回多个结果集的情况。在单个存储过程中包装多个内联表值用户定义函数可以实现此目的。

EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')

最简单的解决方案:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

如果您不知道架构,则可以执行以下操作。 请注意,此方法存在严重的安全风险。

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

当存储过程返回很多列并且您不想手动 “创建” 临时表来保存结果时,我发现最简单的方法是进入存储过程并在存储过程中添加 “into” 子句。最后一个 select 语句,然后在 where 子句中添加 1 = 0。

运行存储过程一次,然后返回并删除刚添加的 SQL 代码。现在,您将有一个与存储过程的结果匹配的空表。您可以为临时表 “创建脚本表”,也可以直接将其直接插入该表。

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;

您的存储过程是否仅检索数据或也对其进行修改?如果仅将其用于检索,则可以将存储过程转换为函数并使用公用表表达式(CTE),而不必声明它,如下所示:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

但是,无论需要从 CTE 检索什么,都应仅在一条语句中使用。您不能with temp as ...并在几行 SQL 之后尝试使用它。您可以在一条语句中包含多个 CTE,以进行更复杂的查询。

例如,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)

如果存储的 proc 的结果表太复杂而无法手动键入 “create table” 语句,并且您不能使用 OPENQUERY 或 OPENROWSET,则可以使用 sp_help 为您生成列和数据类型的列表。获得列列表后,只需对其进行格式化即可满足您的需求。

步骤 1:在输出查询中添加 “进入 #temp”(例如,“从 [...] 中选择到 #temp”)。

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

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

创建临时表后,在临时表上运行 sp_help 以获得列和数据类型(包括 varchar 字段的大小)的列表。

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

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

步骤 4:插入到新创建的表

现在,您将拥有与该线程中描述的其他解决方案类似的查询。

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

INSERT INTO @t 
Exec spMyProc

此技术还可用于将临时表( #temp )转换为表变量( @temp )。尽管这可能不仅仅是自己编写create table语句,而是更多的步骤,但它可以防止大型进程中出现诸如打字错误和数据类型不匹配之类的手动错误。与最初编写查询相比,调试输入错误可能要花费更多时间。