How to Insert Results of Stored Procedure into a Temporary Table? – Interview Question of the Week #124

Question: How to Insert Results of Stored Procedure into a Temporary Table?

Answer: Very simple question, but the answer is sometimes not as easy as we want.

Here is a simple script which demonstrates how we can insert the result of the stored procedure into a temporary table without pre-creating the temporary table before the stored procedure is executed.

However, before we do this task, we have to do to enable ad hoc distributed queries.

Step 0: Create a Stored Procedure

USE TempDB
GO
-- Create a Stored Procedure
CREATE PROCEDURE TestSP
AS
SELECT 1 AS Col1, 2 AS Col2
GO
-- Execute Stored Procedure
EXEC TestSP
GO

Step 1: Enable Ad Hoc Distributed Queries

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

Step 2: Insert into Temp Table

-- Insert into Temp Table
SELECT *
INTO #TempTable
FROM OPENROWSET('SQLNCLI', 'Server=(local)\SS14;Trusted_Connection=yes;','EXEC TempDB.dbo.TestSP')
GO
-- Select Data from Temp Table
SELECT *
FROM #TempTable
GO

Please note that in the above query you will have to change the name of your server name so the query can connect and work.

Step Cleanup: Drop Table

You can clean up the temporary table by dropping it.

-- Clean up
DROP TABLE #TempTable
GO

Let me know what you think of this method. If there is any other efficient method do let me know and I will be happy to blog about the same.

Reference: Pinal Dave (http://blog.SQLAuthority.com)