Inserting Data from a Stored Procedure to a temp Table

     It is some time needed that, we need to get the data from a Stored Procedure and insert it in to a Physical table or in a temp table. Here are some tricks we can apply..

1)
 To get the above task done, we need to use OPENROWSET command.
 And note that we need to enable 'Ad Hoc Distributed Queries' property, if not already enabled.

CREATE PROC SPtoTempTable
    @USERID     VARCHAR(50)
AS
BEGIN
          SELECT     BRANCHCODE, USERNAME
          FROM       Test.dbo.MstUsers (NOLOCK)
          WHERE    USERID = @USERID
END
GO

sp_configure  'Show Advanced Options', 1
GO

RECONFIGURE
GO

sp_configure  'Ad Hoc Distributed Queries', 1
GO

RECONFIGURE
GO

SELECT * INTO #MyTempTable
FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;', 'EXEC SPtoTempTable ''UID0001''')
GO

 SELECT * FROM #MyTempTable
GO

2) 
You can also use OPENQUERY Command to get the same job done.

SELECT  * INTO #MyTempTable FROM OPENQUERY(TheServerName, 'EXEC SPtoTempTable ''UID0001''')
GO

But for this we may get an error, if the server had not enabled 'DATA ACCESS' property. So do the below task first.

EXEC sp_serveroption 'TheServerName', 'DATA ACCESS', TRUE
GO

3)  
And here is the simplest way to get the job done.

IF OBJECT_ID('TEMPDB..#MyTempTable') IS NOT NULL
        DROP TABLE  #MyTempTable
GO

CREATE TABLE #MyTempTable
     (
         COL1      VARCHAR(100)
        ,COL2      VARCHAR(100)
     );
GO

INSERT INTO #MyTempTable Exec SPtoTempTable 'UID0001'
GO

SELECT * FROM  #MyTempTable
GO