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.

Answer 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

Answer 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

Answer 3

You dont need to create a temporary table, you can do it with single query by creating temporary view like this

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

The temporary view disappears as soon as the statement finishes execution

Answer 4

-- Well, declare a temp table or a table var, depending on the number of rows expected -- from the SP. This table will be basically the result set of your SP.

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

-- Get the result set of the SP into the temp table.

INSERT @spResult EXEC STORED_PROC;

-- Now you can query the SP's result set for ID and Value;

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