当过程在SQL Server 2014中返回多个表时，将第一张表插入临时表中(Insert first table into temp table when procedure returns mul

当过程在SQL Server 2014中返回多个表时，将第一张表插入临时表中(Insert first table into temp table when procedure returns multiple tables in SQL Server 2014)

我有2个过程proc_Data ， proc_FetchData 。

我从proc_FetchData调用proc_FetchData 。 proc_Data返回2个表。 我想在第二个过程中仅将第一个表插入临时表中并进一步使用它。

问题是我无法以任何方式更改proc_Data ，因为这是我们应用程序各个部分中使用的非常旧的过程。

示例代码以供参考

create procedure proc_Data As Begin select 'Apples' select 'Oranges','Grapes' end create procedure proc_FetchData As Begin create table #temp(Data varchar(30)) insert into #temp exec Test_proc select * from #temp end

我正在使用SQL Server 2014 - 有什么方法可以实现这一点？ 提前致谢。

I have 2 procedures proc_Data, proc_FetchData.

I'm calling proc_Data from within proc_FetchData. proc_Data returns 2 tables. I want to insert only the first table into a temp table in my second procedure and use it further.

The problem is I cannot change proc_Data in any way as this is a very old procedure used in various parts of our application.

Sample code for reference

create procedure proc_Data As Begin select 'Apples' select 'Oranges','Grapes' end create procedure proc_FetchData As Begin create table #temp(Data varchar(30)) insert into #temp exec Test_proc select * from #temp end

I'm using SQL Server 2014 - is there any way to achieve this? Thanks in advance.

最满意答案

要仅插入第一个表，您可以使用OPENROWSET。

create procedure proc_FetchData As Begin declare @userData TABLE(Data NVARCHAR(30)) INSERT INTO @userData SELECT * FROMOPENROWSET('SQLNCLI','Server=[server];Trusted_Connection=yes;','EXEC [database name].dbo.proc_Data') select * from @userData end

如果您需要访问所有结果集，则需要使用SQLCLR proc： https : //social.msdn.microsoft.com/Forums/sqlserver/en-US/da5328a7-5dab-44b3-b2b1-4a8d6d7798b2/insert-into-table酮-或多结果集从-存储过程？论坛= TRANSACTSQL

To insert only the first table you can use OPENROWSET.

create procedure proc_FetchData As Begin declare @userData TABLE(Data NVARCHAR(30)) INSERT INTO @userData SELECT * FROMOPENROWSET('SQLNCLI','Server=[server];Trusted_Connection=yes;','EXEC [database name].dbo.proc_Data') select * from @userData end

If you need to access all resultsets you will need to use SQLCLR proc: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da5328a7-5dab-44b3-b2b1-4a8d6d7798b2/insert-into-table-one-or-multiple-result-sets-from-stored-procedure?forum=transactsql