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?

How to Insert Results of Stored Procedure into a Temporary Table? - Interview Question of the Week #124 temporarytables-800x232

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)

, , ,
Previous Post
How to Find Running SQL Trace? – Interview Question of the Week #123
Next Post
How to Round Up or Round Down Number in SQL Server? – Interview Question of the Week #125

Related Posts

13 Comments. Leave new

  • Muhammad Idrees
    May 28, 2017 1:42 pm

    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.

    Reply
  • Lokesh Sharma
    May 29, 2017 6:57 pm

    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

    Reply
    • 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.

      Reply
  • Mohanakrishna A
    May 31, 2017 2:45 pm

    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.”

    Reply
  • Phil Doensen
    June 5, 2017 6:16 am

    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.

    Reply
  • John Mitchell
    June 5, 2017 2:28 pm

    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

    Reply
  • John Mitchell
    June 5, 2017 2:32 pm

    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

    Reply
  • 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

    Reply
  • 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!

    Reply
  • if I have my StoredPorcedure expects Paramters. Then how to pass params to the above query?

    Reply
    • SELECT *
      INTO #TempTable
      FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local)\SS14;Trusted_Connection=yes;’,’EXEC TempDB.dbo.TestSP param1 = ‘)
      GO

      Joihn

      Reply
  • Hi, I tried this solution but I got a The OLE DB provider “DB_Server” has not been registered.

    Reply

Leave a Reply

Menu