Inserting the results of a stored procedure to a temp table

Posted on by Cory Foy

Back from Florida. It was a good trip – though we forgot just how hot it is down there in the summer. I got to spend 8 hours cooking barbecue chickens for my old fire department’s Fourth of July parade – it was a great time, and I’ll talk some more about that later.

But one of my coworkers swung by and asked a very simple question – how do you get the results of a stored procedure (that returns a result set) into a table that you can query or join or use somewhere else? The simplest way is that temp tables are your friend, and you just insert the results into your temp table:

CREATE PROC test_proc
AS
SET NOCOUNT ON
SELECT TOP 10 id, name FROM employee

CREATE TABLE #tmp(
[id] int,
name varchar(64)
)

INSERT INTO #tmp
exec test_proc

With that, you can now treat the temp table like any other table:

SELECT *
FROM #tmp

Just don’t forget to clean up when you are done!

DROP TABLE #tmp

22 thoughts on “Inserting the results of a stored procedure to a temp table”

  2. This is ok if Stored procedure returns one table. Can u please tell me how to cope with the sp which returns two tables.

  3. For the stored procedure with a parameter, you could just pass the parameter along:

    CREATE PROC test_proc
    @param1 varchar(128)
    AS
    –…

    INSERT INTO #tmp
    exec test_proc ‘Cory’

    For a stored procedure which returns two tables – I’m not sure what the question is. If your Stored Proc is returning a table type, then you should be able to work like that as if it were any other table. If it is returning the results of two tables in one resultset, and you wanted it to go into two temp tables, the easiest way would be to do exactly what I have here, and then select out of the temp table into the two (temp) tables you want them to go into.

  4. I’ve been trying to figure this out for a day. I can’t believe it was this easy!! Thanks.

  5. Does this work on sybase. I can’t get it to. Do you know what will work similarly on Sybase?

  6. I get Invalid object error #tmp

    when doing this

    INSERT INTO #tmp
    exec test_proc ‘Cory’

  7. For the Invalid Object error – are you doing the Create table call first? Are you using SQL Server or something else?

  8. I also see the same “Invalid object name ‘#temp’.” error when I tried to “create a temp table on the fly using SQL Server 2005.

    The result table in the stored procedure I call has too many columns and I was hoping it is possible to simply grab the result table and dump data into a temp table. So far I haven’t figured out how to.

  9. You would use the OPENQUERY trick when you do not want to create the temp table first. You first need to create a link your server and call it LOCALSERVER, then you could do something like this using the examples in the post:

    SELECT * INTO #new_temp FROM OPENQUERY(LOCALSERVER, ‘EXEC test_proc’)

    I have got this idea from the excellent SQL Server Blog at
    http://www.sommarskog.se/share_data.html

  10. Please do not post more later about your trip to Florida. We only come here for SQL help!!!!

  11. how do I insert all the comlumns from a stored proc results into the new temp table without creating the columns first.

  12. @Cofy, Cna we do this like

    INSERT INTO #tmp
    values(abc,exec test_proc)

    Here ABC is data. is it possible if yes then how if not then any other possiblity???

    Thanks

  14. Hii .. I am getting error saying ‘The FOR XML clause is not allowed in a INSERT statement’ when i try in store the output of the executed stored procedure in a #table.

    Plz help me in solving the above issue.

    Thnkx

  16. Hi Joe,

    If you are selecting from two tables, you can use a UNION statement to pull everything together. If you are needing to pull the results from two different stored procs, insert the results into their own tables, and then doa JOIN or UNION across the two tables.

  19. Pingback: Inserting stored procedure results into a table » Scott's Blog
  20. Pingback: Inserting stored procedure results into a table | Scott's Blog

  21. DECLARE @EmpGroup INT =3 ,
    @IsActive BIT=1

    DECLARE @tblEmpMaster AS TABLE
    (EmpCode VARCHAR(20),EmpName VARCHAR(50),EmpAddress VARCHAR(500))

    INSERT INTO @tblEmpMaster EXECUTE SPGetEmpList @EmpGroup,@IsActive

    SELECT * FROM @tblEmpMaster

Comments are closed.