INSERT INTO variable column count

Question

Unknown number of columns returned from EXEC result.

INSERT INTO #FullTempClr ([Id], [Value1],[Value2], ..., [Value11])
EXEC [report].[TestReport] @Id

Howerver, sometimes TestReport returns Id and 10 values, sometimes Id and 11 and sometimes Id and 1 value. If only Valu1 is available, we need to put null or 0 into other values. Is it possible on this level to do this or the only way is to modify [report].TestReport?

SQL Server 2008 r2

you should add that logic into your stored procedure, so that it always returns the same number of columns — Lamak, Mar 08 '16 at 15:39
I agree with Lamak this logic should be taken care of in a stored procedure. — jkdba, Mar 08 '16 at 15:43
Possible duplicate of [Exec stored procedure into dynamic temp table](http://stackoverflow.com/questions/20280111/exec-stored-procedure-into-dynamic-temp-table) — Ralph, Mar 08 '16 at 16:06

score 0 · Answer 1 · edited May 23 '17 at 11:59

I think the best way for this is to encapsulate all the logic into a stored procedure or you can use something like this:

SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
GO
SELECT * INTO #TempTable 
FROM OPENROWSET
('SQLNCLI','Server=YourLocalServer;Trusted_Connection=yes;',
    'EXEC YourStoredProcedure')
GO
SELECT * FROM #TempTable
GO
DROP TABLE #TempTable

you can also take a look at this post Stored Procedure with dynamic result into temp table

INSERT INTO variable column count

1 Answers1