Put stored procedure result set into a table-- any shortcuts besides INSERT INTO...EXEC?

Question

Hi everyone,

I'm creating a temp table that's the result of a stored procedure's result set. Right now I'm using the create table statement, followed by insert into....exec.

It gets the job done but I was curious if there are other ways of going about this? I wish it were possible to run a select into, with the stored procedure's result set serving the role of the select statement, so that I wouldn't have to write a create statement beforehand (so that if the stored proc's columns change, there wouldn't be any modifications needed.)

If there are other ways to go about this that might fit my needs better I'd love to hear about them. Thanks very much.

Answer 1

There are a couple of ways: Table variables and CTEs. There are a benefits to each and your scenario tends to lead me to believe that a Temp Table is the best option if the source data is substantial. If it's not, I would lean towards a CTE.

Answer 2

One trick:

SELECT * FROM OPENQUERY(ThisServerName, 'EXEC myDB.dbo.myStoredproc') 

Stored procs are not tables or views so you can't expect a recordset like you would a table or view. You comment about "if the stored proc columns change" is overlooking this fact: it shouldn't change