Store Procedure Output in a Table

/******************************************************************************
* 
* Store Procedure Output in a Table 
* 
******************************************************************************/

--======= First Technique

    --- Create Temporary StoreProcedure
    CREATE PROCEDURE Tmp_SPGetAddress 
    AS
        SELECT AddressID,AddressLine1,City from Person.Address WHERE City='Burnaby'
    GO

    --- Create Temporary Table to Store table
    CREATE TABLE Tmp_TBAddres
    (TA_AddressID int,TA_AddressLine1 NVARCHAR(120) ,City NVARCHAR(60))
    GO


    --- Insert data in Table
    INSERT INTO Tmp_TBAddres
    EXEC Tmp_SPGetAddress
    GO

    --- Check the Data in table
    SELECT * FROM Tmp_TBAddres


    -- Don't forget to cean
    DROP PROCEDURE Tmp_SPGetAddress
    DROP TABLE Tmp_TBAddres
    GO





--======= Second Technique


--- Create Temporary StoreProcedure
    CREATE PROCEDURE Tmp_SPGetAddress 
    AS
        SELECT AddressID,AddressLine1,City from Person.Address WHERE City='Burnaby'
    GO


    --- Here we use OPENQUERY. Basically, OPENQUERY is used to execute any T-SQL or Storeproce on linkServer
    
    --- Syntax :: -    OPENQUERY ([ServerName],'[DatabaseName],[SchemaName].[ProcedureName]')
    
    SELECT * INTO #Tmp_TBAddres FROM (        
        SELECT * FROM OPENQUERY([MY_SERVER],'AdventureWorks.dbo.Tmp_SPGetAddress')) AS TEMP_TABLE
    GO
    
    /*
        Sometime this command show the Error message : 
                Msg 7411, Level 16, State 1, Line 1
                Server 'MY_SERVER' is not configured for DATA ACCESS.
        That time use this T-Sql :
                Exec sp_serveroption [MY_SERVER], 'data access', 'true'
    */    
    --- Check the data in table
    SELECT * FROM #Tmp_TBAddres
    GO     

    -- Don't forget to cean
    DROP PROCEDURE Tmp_SPGetAddress
    DROP TABLE #Tmp_TBAddres
    GO