如何在openquery中使用SQL Server表到Oracle数据库？

I have a database on SQL Server and would like to use a column in one of my tables in a linked server openquery I'm running to an Oracle database in order to match values between the two and insert the result into columns in my table in SQL Server .

我有一个关于SQL Server的数据库，并希望在链接服务器openquery中的一个表中使用一列我正在运行到Oracle数据库，以便匹配两者之间的值，并将结果插入到我的表中的列中SQL Server。

Essentially I want it to be like this:

基本上我希望它是这样的：

 SELECT col1, col2, col3, col4
 FROM OPENQUERY(link, 'SELECT * FROM Oracle_Table 
                       WHERE ID = MSSQL.dbo.table.ID`)

So I'd like to be able to use my internal table column values to query an external database. They are related tables but different systems.

所以我希望能够使用我的内部表列值来查询外部数据库。它们是相关的表，但不同的系统。

Would it be possible to get a big list of the values in the SQL Server table column and use it as a variable in the Oracle query? I've searched extensively online but haven't been able to find this one.

是否可以获取SQL Server表列中的大值列表并将其用作Oracle查询中的变量？我在网上广泛搜索但未能找到这个。

1 个解决方案

#1

You can't pass parameters like I wanted to, but I ended up creating a bunch of queries in Powershell using a for loop and variables within the string to create my large query, then put a UNION ALL after each SELECT FROM OPENQUERY()

你不能像我想的那样传递参数，但我最终在Powershell中创建了一堆查询，使用for循环和字符串中的变量来创建我的大查询，然后在每个SELECT FROM OPENQUERY（）之后放入一个UNION ALL