SQL - How to insert results of Stored_Proc into a new table without specifying columns of new table?

Question

Using SQL Server 2005, I'd like to run a stored procedure and insert all of the results into a new table.

I'd like the new table to have its columns automatically configured based upon the data returned by the stored procedure.

I am familiar with using the SELECT ... INTO syntax:

SELECT * INTO newtable FROM oldtable

Is this possible?

Edit for clarification: I'm hoping to accomplish something like:

Select * INTO newtable FROM exec My_SP

Answer 1

A variation of the same is

create table somename
  select * from wherever;

Answer 2

The only way to do this is w/ OPENROWSET against the local server:

SELECT * INTO #temp 
FROM OPENROWSET (
    'SQLOLEDB'
  , 'Server=(local);TRUSTED_CONNECTION=YES;'
  , 'SET FMTONLY OFF EXEC database.schema.procname'
  ) a

But this is kind of a last-ditch-gotta-do-it-damn-the-consequences kind of method. It requires elevated permissions, won't work for all procedures, and is generally inefficient.

More info and some alternatives here: http://www.sommarskog.se/share_data.html

Answer 3

This seems like a horrible design. You're really going to create a new table to store the results of a stored procedure, every time the stored procedure is called? And you really can't create the table in advance because you have absolutely no idea what kind of output the stored procedure has? What if the stored procedure returns multiple resultsets? What if it has side effects?

Okay, well, if that's what you really want to do...

One way to accomplish this is to use your local server as a linked server and utilize OPENQUERY. First you need to make sure your local server is configured for data access:

EXEC sp_serveroption 'local server name', 'DATA ACCESS', true;

Then you can do something like this:

SELECT * INTO dbo.newtable
FROM OPENQUERY('local server name', 'EXEC yourdb.dbo.yourproc;');

PS How are you going to write code that is going to perform SELECT INTO into a new table name every time (because you can only do SELECT INTO once)? Dynamic SQL? What happens if two users run this code at the same time? Does one of them win, and the other one just gets an error message?