x

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?

more ▼

asked Oct 26, 2009 at 07:05 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.

more ▼

answered Oct 26, 2009 at 07:35 PM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

(comments are locked)
10|1200 characters needed characters left

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') 
more ▼

answered Oct 26, 2009 at 08:01 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Rob, I forgot all about this technique, so I'm voting you up.
Oct 27, 2009 at 09:48 AM Bob Hovious
(comments are locked)
10|1200 characters needed characters left

Yes you can:

INSERT INTO MyTable EXECUTE MyProc

where the structure of MyTable matches the output of MyProc

more ▼

answered Oct 26, 2009 at 07:25 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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

insert into #temptbl exec sp_who2

select * from #temptbl

drop table #temptbl
more ▼

answered Oct 26, 2009 at 07:26 PM

sp_lock gravatar image

sp_lock
9.3k 26 28 31

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 27, 2009 at 12:08 AM

kplcjl gravatar image

kplcjl
1

Not true. This will work with multiple result sets so long as they are all compatible with the output table (little known -T-SQL secret).
Oct 27, 2009 at 01:31 AM RBarryYoung
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x415
x109

asked: Oct 26, 2009 at 07:05 PM

Seen: 10999 times

Last Updated: Oct 26, 2009 at 07:05 PM