如何将存储过程 #1 的结果放入存储过程 #2 中的临时表中 [英] How to get results of stored procedure #1 into a temporary table in stored procedure #2
问题描述
我正在尝试将多个存储过程的结果合并到一个临时表中.各种存储过程的结果具有相同的列结构.本质上,我想 UNION ALL 各种存储过程的结果.一个重要的事实:每个存储过程都创建一个临时表来存储其数据,每个返回的结果都基于对临时表的选择:
I am trying to combine the results of several stored procedures into a single temporary table. The results of the various stored procedures have the same column structure. Essentially, I would like to UNION ALL the results of the various stored procedures. A significant fact: each of the stored procedures creates a temporary table to store its data and the results each returns are based on a select against the temporary table:
create proc SP1
as
.
. <snip>
.
select * from #tmp -- a temporary table
请注意,如果 SP1 中的选择针对临时表,则 select * from OPENQUERY(server, 'exec SP1')
不起作用(请参阅 这个问题了解详情),是否有另一种方法来处理不同的存储过程,SP2,把SP1的执行结果放到临时表中?
Noting that select * from OPENQUERY(server, 'exec SP1')
does not work if the select in SP1 is against a temporary table (see this question for details), is there another way for a different stored proc, SP2, to get the results of executing SP1 into a temporary table?
create proc SP2 as
-- put results of executing SP1 into a temporary table:
.
.
.
注意:无法修改 SP1(例如,将其结果存储在具有会话范围的临时表中).
NOTE: SP1 cannot be modified (e.g. to store its results in a temporary table with session scope).
推荐答案
创建临时表,使其适合存储过程的结果.
Create your temporary table such that it fits the results of your stored procedures.
假设你的温度.表被称为#MySuperTempTable",你会做这样的事情...
Assuming your temp. table is called "#MySuperTempTable", you would do something like this...
INSERT INTO #MySuperTempTable (Column1, Column2)
EXEC SP1
这应该可以解决问题.
这篇关于如何将存储过程 #1 的结果放入存储过程 #2 中的临时表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!