Using the results of a query in OPENQUERY

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.

From stackoverflow

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

  Jason Baker : But wouldn't that pull in the entire table unless I know the ids that I need from the first query?

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

• 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.