CODEWITHSUNDEEP
sql-servert-sql
LoudNPossiblyWrong
LoudNPossiblyWrong

Reputation: 3893

SQL Server 2008: SELECT * INTO TMP from stored procedure

I wish to do the following: 

select * into tmptbl from sometable

EXCEPT 'sometable' is a stored procedure that returns a result set AND editing the stored procedure to suit my goal is not an option. ALSO i may or may not know the columns and types of what the procedure returns.

Basically i am looking for a proper way of doing this:

select * into tmptbl from exec someSP

Is this even possible, if so, how?

Upvotes: 1

Views: 2975

Answers (1)

SQLMenace
SQLMenace

Reputation: 135111

yes it is possible with a loopback query like this

SELECT * INTO #tmptbl 
    FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;'
   ,'set fmtonly off exec DatabaseName.dbo.someSP')

More example here: Store The Output Of A Stored Procedure In A Table Without Creating A Table

Be aware that this has to be turned on first, see here: How to enable xp_cmdshell and Ad Hoc Distributed Queries on SQL Server 2005

Upvotes: 5

Related Questions