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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Returns recordset from EXEC stored procedure
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 08/26/2005 :  08:12:59  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

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."

Constraint Violating Yak Guru

483 Posts

Posted - 08/26/2005 :  08:41:33  Show Profile  Reply with Quote
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.

Sean Roussy

Thank you, drive through
Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 08/26/2005 :  08:57:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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


Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000