Insert results of a stored procedures into a temporary table

I found many people asking similar questions on stackexhange and other places. Of all the methods, I liked this answer from stackoverflow which uses OPENROWSET. Below is the method (I didn't write this, just saving it here for my future reference. Full credit goes to the original posted on StackOverflow) You first need to enable adhoc distributed queries using sp_configure in order to use openrowset.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO


-- Then use the openrowset query to insert into temp table

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\BINYOGA;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

I was reading the SQL Server Bible yesterday and learned that we can insert the results of a stored procedure into a table using the below syntax

INSERT INTO dbo.MyTable[(columns)]
  EXEC StoredProcedure Parameters;
 

So when I tried to test that, there was an error -

So, it looks like you are not allowed to use a temptable there. You need to create the table first (it can be a temp table) so that it has the right number of columns as the output.

-- Lets create a simple stored procedure
CREATE PROCEDURE dba_databaselist
AS
SELECT name FROM sysdatabases

--Create the temp table
CREATE TABLE #MyTempTable
(
 name varchar(30),
 
)

--insert from stored procedure

INSERT #myTempTable
 EXEC dba_databaselist

-- verify

SELECT * FROM #myTempTable

UPDATE: Here is another way to do it. Declare a table variable and insert the results of stored procedure to the table.

 

-- Lets create a stored procedure. I am gonna use AdventureWorks database
USE AdventureWorks
GO
CREATE PROC dba_Person
AS
SET NOCOUNT ON;
SELECT FirstName, Lastname 
FROM Person.Person;
--another result set
SELECT FirstName, Lastname
FROM Family.dbo.Person
RETURN;

--Lets execute the stored procedure

Exec AdventureWorks.dbo.dba_Person

-- You'll see two result sets, now lets declare a table variable and insert the data from result sets

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    FirstName varchar(30),
 LastName varchar(30)
    );

-- Now, INSERT/EXECUTE command
INSERT @MyTableVar
 EXEC dbo.dba_Person
-- Verify
SELECT * FROM @MyTableVar

NOTE: I have emulated examples from SQL Server Bible 2008 book in this post.