How can I put SP results into temp table in SQL server

Question

I have tried this:

select * into #temp124
exec usp_GetTagDetails @UserId=1,@IsActiveOnly=1,@IsParentPathRequired=1

But this isn't working. Can someone help me to put SP results into temporary table?

Answer 1

At first enable few options:

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

Then you can use OPENROWSET:

SELECT * INTO #Temp124
FROM OPENROWSET('SQLNCLI', 'Server=(local)\InstanceName;Trusted_Connection=yes;',
     'EXEC usp_GetTagDetails @UserId=1,@IsActiveOnly=1,@IsParentPathRequired=1')

Another way to create table to store data from SP manually, but you should know exactly what data this SP returns.

CREATE TABLE #temp124 (
    Col1 int,
    Col2 nvarchar=(max),
    ...etc
)

INSERT INTO #temp124
EXEC usp_GetTagDetails @UserId=1,@IsActiveOnly=1,@IsParentPathRequired=1