How to put the result of a stored procedure into a table variable?

Question

I am using SQL Server 2005.

My stored procedure returns 100 columns and has 10 pages.

I would need to return only 5 columns and not want to duplicate 10 pages of the stored procedure by creating a new stored procedure.

I would like to avoid defining a new table variable with 100 columns! and I would like to avoid the definition of LinkServer and use OPENROWSET, because the server name, etc. should not be hard-coded.

Is there an easier / better way?

If so, how to write it? The following code does not work:

select ID, Title, (the remaining 3 columns) from exec dbo.sp_myName 

Answer 1

You can create a temporary table with all the columns returned by the stored procedure, and then use:

 Insert Into #TempTable Exec dbo.sp_myName Select ID, Title,... From #TempTable