April 28, 2008 at 10:25 am
i am retrieving data from oracle and trying to put the result set into a temp table but the insert into the temp table does not work. the oracle query does work. i tried using memory,global and temp tables with no success. i also made sure the data transaction is turned on in the servers and my local computer. example of the code:
SELECT * INTO #DailyPons FROM EXECUTE
('select p.pon, pon_sales_rep, p.pon_macnum
from (select pon, max(pon_ver) as pon_ver from local_pon group by pon) p2 join local_pon p on p2.pon=p.pon and p2.pon_ver = p.pon_ver
where pon_status =''7''') AT CUSTOMER;
April 28, 2008 at 10:41 am
You have to create the temp table first and then insert into it.
April 28, 2008 at 10:47 am
If you have the oracle server as a linked server you can use
Select * into #temp from OPENQUERY (YourOracleSvr, 'Select *from yourtable')
hmaharaj (4/28/2008)
i am retrieving data from oracle and trying to put the result set into a temp table but the insert into the temp table does not work. the oracle query does work. i tried using memory,global and temp tables with no success. i also made sure the data transaction is turned on in the servers and my local computer. example of the code:SELECT * INTO #DailyPons FROM EXECUTE
('select p.pon, pon_sales_rep, p.pon_macnum
from (select pon, max(pon_ver) as pon_ver from local_pon group by pon) p2 join local_pon p on p2.pon=p.pon and p2.pon_ver = p.pon_ver
where pon_status =''7''') AT CUSTOMER;
April 28, 2008 at 10:51 am
i did create the temp table first then try to insert the data into it. as for using open quesry i am trying to get away from it. if i use open query it takes approx. 1min 45 sec to bring back my result. if ii use EXEC it takes 14secs. i am trying to optomize existing code.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy