Search code examples
sqlsql-serverdatabaseoracle-databaselinked-server

How can I use a SQL Server table in an openquery to an Oracle database?

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 .

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.

Solution

  • 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()