Inserting row with Procedure

Question

Inserting row with Procedure

jdingle

SSC Rookie

Points: 38
More actions
June 25, 2008 at 2:56 pm

#187512

I would like to no how to create a temporary table from a procedures output. I will execute different procedures with differing number of columns being returned.
For example
Insert into #temptable
Exec TestProc1
This would be no problem if I create the #temptable prior to the execution with the correct number of columns. I even checked the sys.sql_dependencies table by it does work across databases.

Viewing 11 posts - 1 through 11 (of 11 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Am I mistaken, or did you answer your own question?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jdingle SSC Rookie Points: 38 More actions · Answer 2

No the question I didn't answer my own question ... The question is how to I know the number of columns a procedure will return.

GSquared SSC Guru Points: 260824 More actions · Answer 3

The best way is to check the procedure. That way, you can also check what data types each column is, etc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 4

That's what the OP is trying to avoid, Gus... OP wants the temp table to size itself and it's columns like it would using a SELECT/INTO except the OP wants to use INSERT/EXEC to do it.

I don't believe there's a simple way to do this...

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

well - I don't know if this qualifies as "simple", but there is a way....Use OPENQUERY.

Of course, in order for that to work, you'd need to set up an alias to the instance, then link to the alias as a linked server, but hey - once you do that - you can do fun things like SELECT...INTO on a stored procedure output.

Something like this:

select *

into #fun

from openquery(bob,'exec test.dbo.joey')

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

Dang it... nicely done, Matt... I totally forgot that OpenQuery could have an EXEC in it. :blush:

--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)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 7

Bugger... this works just fine (I changed my servername in the code)....

drop table #Fun

GO

select *

into #fun

from openquery([myserver],'exec master.dbo.sp_who')

select * from #fun

... and so does this...

exec master.dbo.xp_DirTree [C:\],1,1

... but this doesn't...

drop table #Fun

GO

select *

into #fun

from openquery([myserver],'exec master.dbo.xp_DirTree [C:\],1,1')

select * from #fun

Any ideas on how to get that very use bit o' heaven to run into a temp table like that?

--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)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

Never mind... I got it... took a little trickery...

drop table #Fun

GO

select *

into #fun

from openquery([myserver],'SET FMTONLY OFF exec master.dbo.xp_DirTree [C:\],1,1')

select * from #fun

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Never would have thought of THAT one....

Nice part is - by doing just the selects through OLEDB - perf shouldn't suffer too much.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 10

OpenQuery is what I was going to suggest as well, if you need this to be dynamic.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon