Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Populate a Temporary Table

Posted on 2006-06-22
4
Medium Priority
?
1,437 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 1500 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Are you an Exchange administrator employed with an organization? And, have you encountered a corrupt Exchange database due to which you are not able to open its EDB file. This article will explain all the steps to repair corrupt Exchange database.
Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

722 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