question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

How do I insert the result of a stored procedure into table

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?

t-sqlstored-proceduresinsert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bob Hovious avatar image
Bob Hovious answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Yes you can:

INSERT INTO MyTable EXECUTE MyProc

where the structure of MyTable matches the output of MyProc

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

You can use the insert statement combind with EXEC...e.g.

insert into #temptbl
exec sp_who2

select *
from #temptbl

drop table #temptbl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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')
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

kplcjl avatar image
kplcjl answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.