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.
| Author |
Topic |
|
TimK
Starting Member
3 Posts |
 Posted - 2003-02-25 : 11:38:03
|
| Hi,Is it possible to create a table based on the results of a stored proc? I know I can do this from a table e.g. select * into #temp from myTable but can I do this select * into #temp exec spMyStoredprocThanksTim |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-25 : 11:50:53
|
| I always wonder why people want to do this - sounds like dubious design.Yoyu can do it using openquery defining a linked server pointing at the local serverselect *into #afrom openqquery(self, 'exec spMyStoredproc')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-25 : 11:52:10
|
| Not with that syntax. You'd have to modify the stored procedure to run the SELECT...INTO instead of SELECT. Also, a temp table would not have scope outside of the sproc, once it finishes the table will be dropped unless you use a global temp table. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-25 : 13:11:16
|
| I use the following to get information about a directory:Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'Hope this helpsBrett8-) |
|
|
|
TimK
Starting Member
3 Posts |
Posted - 2003-02-26 : 04:31:27
|
quote:
I always wonder why people want to do this - sounds like dubious design.
I have a report that uses data in a joined lookup table as the coloumn headings. This lookup table's data can be added to which would mean I would have to keep updating the proc. |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-02-26 : 04:55:51
|
| You can do one of the followinginsert into #temp exec spMyStoredproc Or, if you can re-write spMyStoredproc as a table-function or inline-function, you can do SELECT * FROM dbo.myTableFunction(param list...)like this you can skip the #temp table. If you are using SQL 2000, you could also replace the #temp table with a @table_datatype. Bambola. |
|
|
|
TimK
Starting Member
3 Posts |
Posted - 2003-02-26 : 06:17:47
|
quote:
You can do one of the followinginsert into #temp exec spMyStoredproc
But this assumes you know how many columns to define when creating the temp table. I could potentially have any number of columns depending on data in the lookup table. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
Dynamic Table Creation