Insert into the table selects the result set from the stored procedure, but the number of columns is not the same

Question

I need something like that, of course, does not work.

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

  exec MySPReturning10Columns

)

I wanted to populate table 1 from the result set returned MySPReturning10Columns. Here SP returns 10 columns, and the table has only 2 columns.

The following method works as long as the table and the result set from SP have the same number of columns, but in my case they do not match.

INSERT INTO TableWith2Columns 
  EXEC usp_MySPReturning2Columns;

In addition, I want not to add a "." as a linked server to make openquery and openrowset work anyway.

Is there a way to not define the strucutre table in the temp table (all columns with data types and length)? Something like CTE.

Answer 1

:

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

Answer 2

, , , .

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

Answer 3

- , var, - . SP.

DECLARE @spResult AS TABLE
(
    ID INT,
    VALUE FLOAT,
    ....
);

- SP temp.

INSERT @spResult EXEC STORED_PROC;

- SP ID Value;

INSERT Table1 (ID, VALUE) 
SELECT ID, VALUE FROM @spResult;

Answer 4

, , ,

with tempView as EXEC MySPReturning10Columns insert into Table1 select id, value from tempView

The temporary view disappears as soon as the statement completes execution