I have a stored procedure that returns a result set to me. I want to insert those results into a table. Can I do this?
asked
How do I insert the result of a stored procedure into table
You can only insert the result set from a stored procedure into an existing table whose schema matches the output from the procedure. The syntax is as follows:
INSERT INTO SomeTable
EXEC SomeProcedure
There is also a solution using OPENROWSET which Rob Farley mentioned in an answer below.
Yes you can:
INSERT INTO MyTable EXECUTE MyProc
where the structure of MyTable matches the output of MyProc
You can use the insert statement combind with EXEC...e.g.
insert into #temptbl
exec sp_who2
select *
from #temptbl
drop table #temptbl
You can also use OPENROWSET to turn the results of a stored procedure into a table-expression, and then use SELECT...INTO to create a new table with the results. Bear in mind you can't do this with sp_who2 because it has two columns with the same name (and if you look in the source for it, you'll see it's for 'right-scrolling users'.
I'll put an example together when I'm not on my phone.
Edited: Here's that example...
SELECT *
INTO #SomeNewTable
FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=yes;','EXEC sp_who')
Note that the procedure MUST produce 1 and only 1 Result set. The only ways I know of to retrieve multiple sets is to use a program using ADO or ADO.Net or manually put the output from query manager into individual files (select all, copy, paste) and import the files.
