Solved

Populate a Temporary Table

Posted on 2006-06-22
4
1,428 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

After hours on line I found a solution which pointed to the inherited Active Directory permissions . You have to give/allow permissions to the "Exchange trusted subsystem" for the user in the Active Directory...
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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