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
how about stored procedure with parameter. Any Solution?
This is ok if Stored procedure returns one table. Can u please tell me how to cope with the sp which returns two tables.
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.
I’ve been trying to figure this out for a day. I can’t believe it was this easy!! Thanks.
Does this work on sybase. I can’t get it to. Do you know what will work similarly on Sybase?
I get Invalid object error #tmp
when doing this
INSERT INTO #tmp
exec test_proc ‘Cory’
For the Invalid Object error – are you doing the Create table call first? Are you using SQL Server or something else?
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.
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
Please do not post more later about your trip to Florida. We only come here for SQL help!!!!
how do I insert all the comlumns from a stored proc results into the new temp table without creating the columns first.
@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
how it works for a stored procedure which returns two tables ….
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
please i realy need ur help.. I need to insert two different tables into one for the report generation
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.
How do we do it in Sybase.. Some help please!!!!
Thanks a lot. Easy and worked right away.