在 OPENQUERY 中使用查询结果

发布于 2024-07-11 17:47:21 字数 483 浏览 14 评论 0原文

我有一个链接到 Oracle 数据库的 SQL Server 2005 数据库。我想要做的是运行一个查询，从中提取一些 ID 号，然后找出 Oracle 中的 ID 号。

所以我想获取此查询的结果：

SELECT pidm
FROM sql_server_table

并执行类似的操作来查询 Oracle 数据库（假设上一个查询的结果存储在 @pidms 中）：

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

但我无法想出一个好的方法这。我想我可以对类似于这两个的查询进行内部联接。不幸的是，在有限的时间内需要提取大量记录，因此我认为这不是一个非常高效的选择。

有什么建议么？理想情况下，我希望使用尽可能少的动态 SQL 来完成此操作。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卸妝后依然美 2024-07-18 17:47:21

啊啊，pidms。勾起了不好的回忆！ :)

你可以进行连接，但你会这样做：

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

我不确定你是否可以编写一个 PL/SQL 过程来从 sql 中获取表变量......但也许......不，我对此表示怀疑。

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.

回复收藏 0 原文

横笛休吹塞上声 2024-07-18 17:47:21

将 openquery 结果存储在临时表中，然后在 SQL 表和临时表之间进行内部联接。

回复收藏 0 原文

蝶舞 2024-07-18 17:47:21

我认为您不能进行连接，因为 OPENQUERY 需要纯字符串（如您上面所写）。

回复收藏 0 原文

彩扇题诗 2024-07-18 17:47:21

BG：实际上，通过 OpenQuery JOIN IN SQLServer 到 Oracle 是有效的，避免了 #tmp 表并允许在没有 Param* 的情况下 JOIN 到 SQL - 例如。

[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*

干杯，比尔·吉布斯

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

回复收藏 0 原文

~没有更多了~