0

I have a SQL Server 2005 database that is linked to an Oracle database. What I want to do is run a query to pull some ID numbers out of it, then find out which ones are in Oracle.

So I want to take the results of this query:

SELECT pidm
FROM sql_server_table

And do something like this to query the Oracle database (assuming that the results of the previous query are stored in @pidms):

OPENQUERY(oracledb,
'
SELECT pidm
FROM table
WHERE pidm IN (' +
@pidms + ')')
GO

But I'm having trouble thinking of a good way to do this. I suppose that I could do an inner join of queries similar to these two. Unfortunately, there are a lot of records to pull within a limited timeframe so I don't think that will be a very performant option to choose.

Any suggestions? I'd ideally like to do this with as little Dynamic SQL as possible.

Jason Baker
  • 192,085
  • 135
  • 376
  • 510
  • Can you add the Oracle server as a linked server so that you can use joining syntax instead of calling OPENQUERY? – cmsjr Jan 09 '09 at 23:39

4 Answers4

2

Ahhhh, pidms. Brings back bad memories! :)

You could do the join, but you would do it like this:

select sql.pidm,sql.field2 from sqltable as sql
inner join
(select pidm,field2 from oracledb..schema.table) as orcl
on 
sql.pidm = orcl.pidm

I'm not sure if you could write a PL/SQL procedure that would take a table variable from sql...but maybe.....no, I doubt it.

Sam
  • 7,543
  • 7
  • 48
  • 62
1

Store openquery results in a temp table, then do an inner join between the SQL table and the temp table.

jcollum
  • 43,623
  • 55
  • 191
  • 321
1

I don't think you can do a join since OPENQUERY requires a pure string (as you wrote above).

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
0

BG: Actually JOIN IN SQLServer to Oracle by OpenQuery works, avoiding #tmp table and allowing JOIN to SQL without Param* - ex. 

[SQL SP] LEFT JOIN OPENQUERY(ORADB,
'SELECT  COUNT(distinct O.ORD_NUM) LCNT, 
 O.ORD_MAIN_NUM  
 FROM CUSTOMER.CUST_FILE C
 JOIN CUSTOMER.ORDER_NEW O 
 ON C.ID = O.ORD_ID
 WHERE  C.CUS_ID NOT IN (''2'',''3'') 
 GROUP BY O.ORD_MAIN_MACNUM') LC 
 ON T.ID = LC.ORD_MAIN_ID*

Cheers, Bill Gibbs

BillyGibbs
  • 1
  • 1