Can we copy the results of an executed stored procedure data into temp table directly?

Question

I tried using this

select * into #tempdb  (
exec SSC.usp_abc_data '01/31/2017','L','G','1','17',Null)

It prompts out an error saying that

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

Possible duplicate of [Insert results of a stored procedure into a temporary table](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — Paul Abbott, Feb 06 '17 at 20:02
Its not a duplicate it looks like similar but not@paul Abbott — SantoshKumarReddy Badugula, Feb 06 '17 at 20:21

score 0 · Answer 1 · answered Feb 06 '17 at 20:32

you can do it like so:

SELECT * INTO #TestTableT FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC    [databasename].[schemaname].[sp name]')
-- Select Table
SELECT *
FROM #TestTableT;

so in your case it will be like:

SELECT * INTO  #tempdb FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC    [databasename].SSC.usp_abc_data ''01/31/2017'',''L'',''G'',''1'',''17'',Null')
-- Select Table
SELECT *
FROM #tempdb;

Just remember to include the database name.

if you are getting error in this method enable ad hoc distributed queries by executing following query in SSMS you will need:

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

Answer referenced from here

Can we copy the results of an executed stored procedure data into temp table directly?

1 Answers1