Let's say you're looking to do something like this:
select * into NewTable
from (exec MyProc 5)
Unfortunately, there's not an easy way to do this. The standard fare is to create the table first, then insert the data from the proc:
create table NewTable (data varchar(5) NULL)
insert into NewTable exec MyProc 5
But if the table has a ton of columns, this ain't fun. In addition, the whole reason I usually want to do an select..into from a proc in the first place is to generate a script of the output schema. It's a catch-22.
Update: Andy Warren from sqlservercentral.com clued me into a much simpler way to do this, though the way it works is about the same:
select * into NewTable
from openrowset('SQLOLEDB’, ‘Data Source=MyServer;Database=MyDB;UID=user;PWD=pswd’, ‘exec MyProc 5’)
Note: I did have a case where MyProc creates connection-scope temp tables. In that case, the OpenRowset approach didn't work, but the lengthier linked server approach below worked fine.
Here's the old way, still works, but obviously messier:
sp_addlinkedserver @server='self’, @srvproduct=’’, @provider='SQLOLEDB’, @datasrc=@@SERVERNAME
go
-- You have to explictly allow rpcs to and from your server
-- to get the exec call to work. This may have security implications,
-- consider yourself forewarned. If you run this whole script, ‘self’
-- is dropped here in a sec anyway, so no big deal.
sp_serveroption ‘self’, ‘rpc’, ‘on’
go
sp_serveroption ‘self’, ‘rpc out’, ‘on’
go
-- make sure you use the fully qualified name inside the OpenQuery statement
select * into NewTable
from OpenQuery(self, ‘self.MyDB..MyProc 5’)
go
sp_dropserver ‘self’
go
select * from NewTable
tags: ComputersAndTechnology SqlServer
