≡ Menu

Inserting the results of a stored procedure to a temp table

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

Comments on this entry are closed.

  • Anonymous April 23, 2007, 2:47 pm

    how about stored procedure with parameter. Any Solution?

  • Anonymous October 1, 2007, 2:14 am

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

  • Cory Foy October 1, 2007, 8:51 am

    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.

  • Anonymous November 30, 2007, 7:42 pm

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

  • Anonymous January 31, 2008, 1:27 pm

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

  • Anonymous April 8, 2008, 3:44 pm

    I get Invalid object error #tmp

    when doing this

    INSERT INTO #tmp
    exec test_proc ‘Cory’

  • Cory Foy April 8, 2008, 7:44 pm

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

  • Anonymous August 29, 2008, 5:15 pm

    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.

  • Sonny Nallamilli December 15, 2008, 9:24 pm

    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

  • Phil Edwards August 26, 2009, 2:18 pm

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

  • Zwy May 19, 2010, 6:14 am

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

  • Sanjay May 17, 2011, 7:56 am

    @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

  • RAVI June 23, 2011, 2:09 am

    how it works for a stored procedure which returns two tables ….

  • Kshitija August 24, 2011, 3:24 am

    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

  • joe phiri September 2, 2011, 1:50 am

    please i realy need ur help.. I need to insert two different tables into one for the report generation

  • Cory Foy September 2, 2011, 6:21 am

    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.

  • Khyati November 1, 2011, 8:31 am

    How do we do it in Sybase.. Some help please!!!!

  • Omar December 23, 2011, 11:45 am

    Thanks a lot. Easy and worked right away.

  • URVISH SUTHAR March 31, 2012, 7:01 am

    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

  • Waqas August 25, 2012, 9:20 am