Query Oracle Table In SQL & Put In Temp Table

Hi all,

I am querying a table in oracle, the server connection to the Oracle database is determined by a criteria. Though how can I put the results from the oracle query into a temp table ?

This is the code i'm using for the query:

DECLARE @.cmd VARCHAR(500)
declare @.Year varchar(25)
set @.Year = '2006'

DECLARE @.Link VARCHAR(100)
DECLARE @.Table VARCHAR(100)

select @.Link = Server from tbl_Conn where Area='Floor'
select @.Table = Target_Table from tbl_Conn where Area='Floor'

SET @.cmd =
'
select * from OPENQUERY
(
' + @.Link + ',
''
UPDATE '+ @.Table +'
SET TARGET_VALUE = '+@.Value+'
WHERE Date = '+@.Year'
''
)
'
EXEC (@.cmd)

How do I put the executed results into a TEMP table ?

Rgds,

From your codes its seem that, your are try to update the table which is at remote side & now you want to store the results of the output of this update statmet?

if so then update only returns the "number of row updated", exaple "10 rows updated", you want to store this string ?

or

if you want to store the results return by the select query, then solution may be like this (just an example)

Create Table ##Temp

(

AC_NAME_TEMP varchar(1000)

)

DECLARE @.sql varchar(1000)

SET @.sql = 'SELECT AC_NAME FROM DM.dbo.ACCOUNTS'

Insert ##Temp EXEC (@.sql)

Select * From ##Temp

Drop Table ##Temp

Gurpreet S. Gill

|||

Sorry about the code I showed, i copied across my Update statement instead of the select statement by mistake.

Though I had tried using the code same as yours before & tried again but keep getting this error:

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b].