Using Query Results in OPENQUERY
I have a SQL Server 2005 database linked to an Oracle database. I want to run a query to pull some ID numbers out of it and then figure 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 the results of the previous query are stored in @pidms):
OPENQUERY(oracledb,
'
SELECT pidm
FROM table
WHERE pidm IN (' +
@pidms + ')')
GO
But I'm in trouble with a good choice. I suppose I could do an inner join of queries like these two. Unfortunately, there are many recordings for a limited period of time, so I don't think this would be a very effective option.
Any suggestions? Ideally I would like to do this with as little dynamic SQL as possible.
source to share
Ahhhh, pidms. Brings back bad memories! :)
You can make a 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 can write a PL / SQL procedure that will take a table variable from sql ... but maybe ... no, I doubt it.
source to share
BG: Actually JOIN IN SQLServer to Oracle by OpenQuery, avoiding the #tmp table and allowing JOIN 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
source to share