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
GOPlease 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)
14 Comments. Leave new
Hi Pinal, Good to see this. Once I also used this tip. Another time I had problem with this because I am using this task in another StoredProcedure, and it was working fine on my machine. But the problem comes when I have to distribute my StoredProcedures script to other machines (5-10) depending on some specific scenario. So here I stuck with this because I have to change SERVERNAME/CREDENTIALS for each. Is there any way to overcome this.
Hi Pinal,
Thank you for sharing this. How is it different from the following code. We are using SQL 2005
create table #temptb (col1 int,…)
insert into #tempdb
exec stored_proc parameter1, …
It works fine for us
In your method you will have to create a table first, that means you need to know the exact column count and the data type.
In the method which I suggest you do not need to know any of those.
This Code is not working if the SP has temptable operations inside an sp.
getting error like
“Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘INSERT INTO #AllUsers
SELECT col1,col2,col3
in procedure ‘Gettest uses a temp table.”
My first reaction was to try using this method with system stored procedures, which are notorious for changing between SQL versions.
Using SQL 2016 SP1
sp_who works
sp_who_2 does not
sp_tables does not
Still could be useful given the limitations.
Muhammad, OPENROWSET doesn’t accept variables for its arguments and therefore you’d have to use dynamic SQL. Could get quite messy quite quickly.
John
Pinal, you do indeed need to know the columns and data types in order to create the temp table. But you also need to know the columns in order to query it, so what are we gaining by doing it like this? I’m sure this technique has its place, but I’d advise to think carefully about increasing the surface area by allowing ad hoc distributed queries before using it.
John
Hi Pinal Dave- If sproc returns multiple result sets as below then step2 returns only first select.
How can we get data from 2nd select statement?
CREATE PROCEDURE TestSP
AS
BEGIN
SELECT 1 AS Col1, 2 AS Col2
SELECT 3 AS Col1, 4 AS Col2, 5 AS col
END
GO
Hi Pinal,
Does the sproc need to be created in tempDB, or can it be in any DB? How about a schema other than dbo. I’d assume so, but we all know the dangers of making assumptions!
Thanks in advance!
if I have my StoredPorcedure expects Paramters. Then how to pass params to the above query?
SELECT *
INTO #TempTable
FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local)\SS14;Trusted_Connection=yes;’,’EXEC TempDB.dbo.TestSP param1 = ‘)
GO
Joihn
Hi, I tried this solution but I got a The OLE DB provider “DB_Server” has not been registered.
Your logic will fail when your SP manipulating anything with # (temporary) tables.
Example : create your sp as following
CREATE PROCEDURE TestSP
AS
begin
Create Table #temp (Col1 int,Col2 int)
Insert into #temp values (1,2)
Insert into #temp values (3,4)
SELECT * from #temp
end
I know you may answer with @ tables but it won’t work when I use that @ table in dynamic sql query.
I hope, there is a limitation of SQL on OPENROWSET