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)