| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 08/26/2005 : 08:12:59
|
ActuarialDBA writes "I have a stored procedure "stored_proc_name" which returns a recordset with hundred of columns. Right now, I have a query like this:
CREATE TABLE temp_table(col1, col2,...,col100...)
INSERT INTO temp_table
EXEC stored_proc_name
SELECT TOP 10
FROM temp_table, another_table
DROP TABLE temp_table
Are there any other ways to avoid the first step of creating the temporary table? I'm asking this because sometimes structure of the recordset from the stored_proc_name is unknown, or too much to write them all out, not to mention about the performance side effects. THANKS." |
|
|
Thrasymachus
Constraint Violating Yak Guru
USA
483 Posts |
Posted - 08/26/2005 : 08:41:33
|
that's not a temp table. temp tables are prefaced with #. we can probably optimize this for you but we are going to need to see what your stored proc does, we will need the table definitions and some sample data.
====================================================
Regards,
Sean Roussy
Thank you, drive through |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 08/26/2005 : 08:57:37
|
You cannot do
Select * into #myTable from exec sp
You need to create the temp table and then insert it as what you did
Is it necessary to have 100 columns?
Where do you want to show them all?
I think you need to normalize the requirement
As said explain your requirement clearly
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Returns recordset from EXEC stored procedure
