Execute Into Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Execute Into Statement

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-20 : 20:39:19

I would like to accomplish a execute into statment, can this be done.

For instance a insert into


Select *
Into #TmpTable
from Mytable

I know i can insert the results of my stored procedure if I create the table like so


create Table #tmp1(Col1 int)

Insert Into #Tmp1
Exec Sproc_MyStoredProcedure

But can I do it w/o declaring the columns like in a Select Into Statment, like below (That code obviously doesn't work, but is there something similiar that will accomplish this?


Exec Sproc_MyStoredProcedure
Into #Tmp

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-20 : 21:12:28

No. Don't think there is.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-21 : 10:19:57

May be with the help of Linked server or Openrowset
Serach here
http://weblogs.sqlteam.com/mladenp/

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-21 : 11:17:31

There was a thread on SQL Team about discovering the type of data in an unknown resultset, and using that to create a suitably-shaped table just-in-time.

Can't remember any more about it than that.

Kristen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-21 : 13:36:32

Thanks, I'm surprised sql hasn't accomidated this method yet. Hopfully in the future it will.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-22 : 05:05:35

Yes this can be achieved using OPENROWSET():

Select * into #temp 
from OPENROWSET('SQLOLEDB','Data Source=MyServer;Trusted_Connection=yes;Integrated Security=SSPI', 'Execute DB1..MyProc1')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-22 : 05:30:33

quote:
Originally posted by harsh_athalye

Yes this can be achieved using OPENROWSET():

Select * into #temp
from OPENROWSET('SQLOLEDB','Data Source=MyServer;Trusted_Connection=yes;Integrated Security=SSPI', 'Execute DB1..MyProc1')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks. My guess was correct

Madhivanan

Failing to plan is Planning to fail

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-22 : 05:33:43

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources