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