In the following article we will learn how to insert results of a stored procedure to a table. There are two approaches to do this depending on whether we know the table schema beforehand or not. We will be using the following stored procedure to illustrate the two approaches.

CREATE PROCEDURE GetFileNames
AS
SELECT FileName, FileID
FROM Files
GO

1) Table exist

Since schema is already known we can use the below solution:

CREATE TABLE #Files
(
   [FileName] NVARCHAR(256), 
   [FileID] INT
);

INSERT INTO #Files
EXEC GetFileNames

SELECT * FROM #Files

2) Table does not exist

Since schema is not known we need to use the below solution:

SELECT * INTO #Files FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;','EXEC tempdb.dbo.GetFileNames')
SELECT * FROM #Files

In case of error do check if adhoc queries are enabled. If not we can enable as below:

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