The result of stored procedure to a table, without creating the table manually in SQL Server

Question

What I need to do is, put the results of stored procedure (with lots of parameters) in to a table. Something like that:

SELECT * INTO Table_1 FROM
(
EXEC [MY_DataBase].[dbo].[GET_Report] '%%', '%%', '%%', 'x', 'x', 'x', 'x', 'x', 'x' ....
) X

However this seems to be an incorrect syntax, I searched for it and people are first creating the table manually and after using the INSERT key to put the results. This is something that I can't do, since I ve got lots of parameters and I can't create the table manually.

Answer 1

You can do it if you change stored procedure to user defined function.

Answer 2

You can't use a stored procedure within a SELECT statement like this, meaning you can't create the target table inline at the point of execution using the sproc.

You either need to:

1) create Table_1 first with the correct schema and then do:
INSERT Table_1 (fieldlist) EXECUTE YourSproc....

2) rewrite the sproc as a unser defined function and use that in the SELECT INTO statement:

SELECT *
INTO Table_1
FROM dbo.YourFunction(same params as sproc...) x

Answer 3

You can try this solution, it involves: enabling ad hoc queries, using SELECT..INTO to create a temp table and using OPENROWSET

-- you will need to enable Ad Hoc Remote Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE 
GO

-- this assumes trusted connection
SELECT tmp.*
INTO #mytemptable
FROM OPENROWSET ('SQLOLEDB',
     'Server=SQLVM\SQL01;TRUSTED_CONNECTION=YES;Database=pubs',
     'EXEC sp_who') tmp

-- select from your temporary table
SELECT *
FROM #mytemptable