Using the results of a query in OPENQUERY

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.