Solved

Populate a Temporary Table

Posted on 2006-06-22
4
1,432 Views
Last Modified: 2008-02-01
Hi,
 iam new to Sybase.
 I have a stored procedure,which returns a data set.I'd like to dump the results of this stored procedure,into a temporary table.
  How can i do that?
Thanks.
0
Comment
Question by:pvsbandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Author Comment

by:pvsbandi
ID: 16961821
Hello!!...nobody to clear my doubt?
0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 16976861
Which Sybase product and version? There are three major Sybase databases.

The easiest way, and this will work in all Sybase products, is simply to redirect the result sets of all queries in the procedure into a temp table. (ie. turn all SELECTs into INSERT... SELECTs)

If you do this, you will want to create the temp table in the parent calling environment first.

If you are using ASE of at least 11.5 or above, you could also create a proxy table mapped to the stored procedure, so that issuing a SELECT on the proxt table executes the procedure and displays its result set. It would then be trivial to put this into a temp table.

This second method is complicated so I'll hold off until you confirm the first method isn't sufficient.

Good luck!
0
 

Author Comment

by:pvsbandi
ID: 16978640
Hi,
 Thanks for the reply..iam on ASE 12.5.
  As you said,the first method asks for a structure of a table to be created first,in order to
  populate the data from the stored proc.
  Can you tel me another way,which will simply dump the data returned by the
  stored proc into a temporary table on the fly,without having to create the table.
Thanks1
 
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 16978952
Any temp table that you create in the stored proc does not exist outside the scope of that proc. When the proc finishes executing, then the table is gone.

You could go with my first suggestion and have the first result set in the proc do a SELECT INTO, and all subsequent SELECTs do an INSERT... SELECT. You can use a "persistent" table in tempdb, which unlike #temp tables *will* be visible when the proc is finished...
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question