Insert into Table select result set from stored procedure but column count is not same

Question

I need something like that which is of course not working.

insert into Table1
(
  Id,
  Value
)
select Id, value from
(

  exec MySPReturning10Columns

)

I wanted to populate Table1 from result set returned by MySPReturning10Columns. Here the SP is returning 10 columns and the table has just 2 columns.

The following way works as long as table and result set from SP have same number of columns but in my case they are not same.

INSERT INTO TableWith2Columns 
  EXEC usp_MySPReturning2Columns;

Also, I want to avoid adding "." as linked server just to make openquery and openrowset work anyhow.

Is there a way not to have define table strucutre in temp table (all columns with datatypes and lenght)? Something like CTE.

Solution 1

You could use a temporary table as a go-between:

insert into #TempTable exec MySP
insert into Table1 (id, value) select id, value from #TempTable

Solution 2

You could solve the problem in two steps by doing the insert from the stored procedure into a temporary table, then do the insert selecting just the columns you want from the temporary table.

Information on temporary tables: http://www.sqlteam.com/article/temporary-tables