How to save sp exec result into a temp table

Question

Post reply

How to save sp exec result into a temp table

halifaxdal

SSCoach

Points: 19741
More actions
January 18, 2013 at 1:34 pm

#268835

Hello,
I have a sp where I created a temp table, I need then insert the result of another sp into that table.
I end up write code like this:
set @sql = '
Insert into #BreakDownByCategories
Select * FROM OPENROWSET( ' + '''' + 'SQLNCLI' + '''' + ',' + '''' +
'Server=(local);Trusted_Connection=yes;' + '''' + ',' + '''' +
'SET FMTONLY OFF; SET NOCOUNT ON; exec spGetCategoriesByDocIDAsTable ' + Convert(varchar, @DocID) + '''' + ')'
exec (@sql )
I wonder if there is a better way to do this since OPENROWSET is usually disabled.
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

INSERT INTO #BreakDownByCategories

EXEC dbo.spGetCategoriesByDocIDAsTable @DocID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MyDoggieJessie SSC-Forever Points: 44484 More actions · Answer 2

Jeff Moden (1/18/2013)
INSERT INTO #BreakDownByCategories
EXEC dbo.exec spGetCategoriesByDocIDAsTable @DocID

I believe an accidental typo?

INSERT INTO #BreakDownByCategories

EXEC dbo.spGetCategoriesByDocIDAsTable @DocID

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

MyDoggieJessie (1/18/2013)
Jeff Moden (1/18/2013)
INSERT INTO #BreakDownByCategories
EXEC dbo.exec spGetCategoriesByDocIDAsTable @DocID
I believe an accidental typo?
INSERT INTO #BreakDownByCategories
EXEC dbo.spGetCategoriesByDocIDAsTable @DocID

Thanks for the catch. You are correct.... the second "exec" should not have been there. Was a Copy/Paste error on my part.

Repaired my post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jared SSC Guru Points: 61793 More actions · Answer 4

Jeff Moden (1/18/2013)
INSERT INTO #BreakDownByCategories
EXEC dbo.spGetCategoriesByDocIDAsTable @DocID

Jared
CE - Microsoft

halifaxdal SSCoach Points: 19741 More actions · Answer 5

halifaxdal

SSCoach

Points: 19741

January 21, 2013 at 8:34 am

#1579276

Thank you all for the replies