How to insert results from Stored Procedure to a temp table in SQL Server?

As a database programmer, you may need to get result in table format from a stored procedure and store it to a temp table. Here we will see how to insert results from Stored Procedure to a temp table. There is a small trick involved in doing this. One among the easiest way is to use a OPENROWSET function. This method comes in handy when you are not sure on the type of result set returned from the stored procedure. We will also see couple of common errors and the troubleshooting tips as well.

To start with, let us create a stored procedure which returns a result set.

/* Create a stored procedure */
CREATE PROCEDURE mtbGetPeople
AS
BEGIN
	Select 
		FullName, 
		PhoneNumber, 
		EmailAddress 
		from Application.People
END
GO

Now using OPENROWSET function you can insert results from Stored Procedure to the temp table.

SELECT *
INTO #People
FROM OPENROWSET('SQLNCLI', 
	'Server=MACWINDOWS;Trusted_Connection=yes;',
	'EXEC WideWorldImporters.dbo.mtbGetPeople')
GO

Select * from #People
GO

Drop Table #People
GO

If you notice the OPENROWSET statement, I have specified the stored procedures name along with the database name and schema name. Without the database and the schema name, you may get an error saying “Could not find stored procedure ….”

Troubleshooting

You may get the below errors while working with OPENROWSET. Let us see how to fix them.

1. SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’

In case you get an error as below, then as the error message suggests, you have to switch on Ad Hoc Distributed Queries component in the configuration

Msg 15281, Level 16, State 1, Line 21
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

To switch on Ad Hoc Distributed Queries in global configuration settings, execute this command from the query window.

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

2. Could not find stored procedure

You may get the below error, if you have specified only the stored procedure name without the database and schema name in the EXEC statement. TO avoid this error use the EXEC statement in the format EXEC [Database_Name].[Schema_Name].Stored_Procedure.

Msg 11529, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 20]The metadata could not be determined because every code path results in an error; see previous errors for some of these.Msg 2812, Level 16, State 62, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 20]Could not find stored procedure ‘mtbGetPeople’.

See more SQL Sever Tips.

Reference

• More about OPENROWSET at Microsoft Docs.