0

I have a stored procedure which returns a dataset

Let's say its name is spx and it returns

I'd name
1.   Abc
2.   Def

I want to get it's result in a temp table in another stored procedure dynamically like if in future I change the dataset in my above so it will reflect here

Like this

exec spx

It will execute the SP and I want it's result set in a table

Any help would be much appreciated. Thanks.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Atk
  • 754
  • 1
  • 4
  • 12
  • Does this answer your question? [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) – Preben Huybrechts Sep 07 '20 at 06:51

2 Answers2

1

If you want to define the temporary table you can use standard SQL

CREATE TABLE #tmpTable
(
    ID INT,
    Name nvarchar(50)
)

INSERT INTO #TempTable
EXEC spTest

Select * FROM #TempTable

If you don't want to define the table you can use OPENROWSET

SELECT * INTO #TempTable FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC spTest')

Select * FROM #TempTable
Gary Connell
  • 86
  • 4
0

You can try this:

SELECT  * INTO [temp-table] FROM OPENQUERY("server-name", 'EXEC spx');
Soumendra Mishra
  • 3,483
  • 1
  • 12
  • 38