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

Insert results of a stored procedure into a temporary table

提问by Ferdeen

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table]and without defining [temp table]?

我该怎么做SELECT * INTO [temp table] FROM [stored procedure]？没有FROM [Table]和没有定义[temp table]？

Selectall data from BusinessLineinto tmpBusLineworks fine.

Select来自BusinessLineinto 的所有数据都可以正常tmpBusLine工作。

select *
into tmpBusLine
from BusinessLine

I am trying the same, but using a stored procedurethat returns data, is not quite the same.

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

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

Output message:

输出消息：

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

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

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

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

回答by Aaron Alton

You can use OPENROWSETfor this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

您可以为此使用OPENROWSET。看一看。我还包含了 sp_configure 代码以启用 Ad Hoc 分布式查询，以防它尚未启用。

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

回答by Gavin

If you want to do it without first declaring the temporary table, you could try creating a user-defined function rather than a stored procedureand make that user-defined function return a table. Alternativly, if you want to use the stored procedure, try something like this:

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

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'

回答by Matt Hamilton

In SQL Server 2005 you can use INSERT INTO ... EXECto insert the result of a stored procedure into a table. From MSDN's INSERTdocumentation(for SQL Server 2000, in fact):

在 SQL Server 2005 中，您可以使用INSERT INTO ... EXEC将存储过程的结果插入到表中。来自MSDN 的INSERT文档（实际上是针对 SQL Server 2000）：

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

回答by Christian Loris

This is an answer to a slightly modified version of your question. If you can abandon the use of a stored procedure for a user-defined function, you can use an inline table-valued user-defined function. This is essentially a stored procedure (will take parameters) that returns a table as a result set; and therefore will place nicely with an INTO statement.

这是对您问题的稍微修改版本的答案。如果可以放弃对用户定义函数使用存储过程，则可以使用内联表值用户定义函数。这本质上是一个存储过程（将接受参数），它返回一个表作为结果集；因此将与 INTO 语句很好地放置在一起。

Here's a good quick articleon it and other user-defined functions. If you still have a driving need for a stored procedure, you can wrap the inline table-valued user-defined function with a stored procedure. The stored procedure just passes parameters when it calls select * from the inline table-valued user-defined function.

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

So for instance, you'd have an inline table-valued user-defined function to get a list of customers for a particular region:

例如，您有一个内联表值用户定义函数来获取特定区域的客户列表：

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

You can then call this function to get what your results a such:

然后你可以调用这个函数来得到你的结果：

SELECT * FROM CustomersbyRegion(1)

Or to do a SELECT INTO:

或者做一个 SELECT INTO：

SELECT * INTO CustList FROM CustomersbyRegion(1)

If you still need a stored procedure, then wrap the function as such:

如果您仍然需要存储过程，则将函数包装如下：

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

I think this is the most 'hack-less' method to obtain the desired results. It uses the existing features as they were intended to be used without additional complications. By nesting the inline table-valued user-defined function in the stored procedure, you have access to the functionality in two ways. Plus! You have only one point of maintenance for the actual SQL code.

我认为这是获得预期结果的最“无黑客”的方法。它使用现有的功能，因为它们的目的是在没有额外复杂性的情况下使用。通过在存储过程中嵌套内联表值用户定义函数，您可以通过两种方式访问​​该功能。加！对于实际的 SQL 代码，您只有一个维护点。

The use of OPENROWSET has been suggested, but this is not what the OPENROWSET function was intended to be used for (From Books Online):

已建议使用 OPENROWSET，但这不是 OPENROWSET 函数的用途（来自联机丛书）：

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.

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

Using OPENROWSET will get the job done, but it will incur some additional overhead for opening up local connections and marshalling data. It also may not be an option in all cases since it requires an ad hoc query permission which poses a security risk and therefore may not be desired. Also, the OPENROWSET approach will preclude the use of stored procedures returning more than one result set. Wrapping multiple inline table-value user-defined functions in a single stored procedure can achieve this.

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

回答by Quassnoi

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

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

回答by Tigerjz32

Easiest Solution:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

最简单的解决方案：

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

If you don't know the schema then you can do the following. Please note that there are severe security risks in this method.

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

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

回答by dotjoe

When the stored procedure returns a lot of columns and you do not want to manually "create" a temporary table to hold the result, I've found the easiest way is to go into the stored procedure and add an "into" clause on the last select statement and add 1=0 to the where clause.

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

Run the stored procedure once and go back and remove the SQL code you just added. Now, you'll have an empty table matching the stored procedure's result. You could either "script table as create" for a temporary table or simply insert directly into that table.

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

回答by nitin

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;

回答by FistOfFury

If the results table of your stored proc is too complicated to type out the "create table" statement by hand, and you can't use OPENQUERY OR OPENROWSET, you can use sp_help to generate the list of columns and data types for you. Once you have the list of columns, it's just a matter of formatting it to suit your needs.

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

Step 1: Add "into #temp" to the output query (e.g. "select [...] into #temp from [...]").

第 1 步：将“into #temp”添加到输出查询（例如“select [...] into #temp from [...]”）。

The easiest way is to edit the output query in the proc directly. if you can't change the stored proc, you can copy the contents into a new query window and modify the query there.

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

Step 2: Run sp_help on the temp table. (e.g. "exec tempdb..sp_help #temp")

步骤 2：在临时表上运行 sp_help。（例如“exec tempdb..sp_help #temp”）

After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields.

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

Step 3: Copy the data columns & types into a create table statement

第 3 步：将数据列和类型复制到 create table 语句中

I have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor. Use the column names, sizes, and types to construct a "Create table #x [...]" or "declare @x table [...]" statement which you can use to INSERT the results of the stored procedure.

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

Step 4: Insert into the newly created table

第四步：插入新创建的表

Now you'll have a query that's like the other solutions described in this thread.

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

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

INSERT INTO @t 
Exec spMyProc 

This technique can also be used to convert a temp table (#temp) to a table variable (@temp). While this may be more steps than just writing the create tablestatement yourself, it prevents manual error such as typos and data type mismatches in large processes. Debugging a typo can take more time than writing the query in the first place.

此技术还可用于将临时表 ( #temp) 转换为表变量 ( @temp)。虽然这可能比create table自己编写语句需要更多的步骤，但它可以防止手动错误，例如大型流程中的拼写错误和数据类型不匹配。调试错字可能比首先编写查询花费更多的时间。

回答by SO User

Does your stored procedure only retrieve the data or modify it too? If it's used only for retrieving, you can convert the stored procedure into a function and use the Common Table Expressions (CTEs) without having to declare it, as follows:

您的存储过程是只检索数据还是也修改数据？如果仅用于检索，则可以将存储过程转换为函数并使用公共表表达式 (CTE)，而无需声明它，如下所示：

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

However, whatever needs to be retrieved from the CTE should be used in one statement only. You cannot do a with temp as ...and try to use it after a couple of lines of SQL. You can have multiple CTEs in one statement for more complex queries.

但是，需要从 CTE 检索的任何内容都应仅在一个语句中使用。你不能做 awith temp as ...并尝试在几行 SQL 之后使用它。对于更复杂的查询，您可以在一个语句中使用多个 CTE。

For example,

例如，

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)