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
– Lamak, Commented Mar 8, 2016 at 15:39
I agree with Lamak this logic should be taken care of in a stored procedure. — jkdba
– jkdba, Commented Mar 8, 2016 at 15:43
Possible duplicate of Exec stored procedure into dynamic temp table — Ralph
– Ralph, Commented Mar 8, 2016 at 16:06

Community · Accepted Answer · 2017-05-23 11:59:45Z

0

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

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Mar 8, 2016 at 16:30

jthalliens

5444 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT INTO variable column count

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related