1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dispersion
  • 11
  • 6
  • you cannot pass parameters nor join your mssql table. but you can create the open query with predefined values... see more details here http://stackoverflow.com/questions/3378496/including-parameters-in-openquery – are Jan 07 '16 at 21:03
  • 1
    The Way I solve the problem when "MSSQL.dbo.table.ID" is just one value is run the query dynamically. build your Select in a string and run it using EXEC (@SQLStr). The comment by @are points you to a post that explains it more in detail. – JohnG Jan 07 '16 at 21:07
  • @JohnG The MSSQL.dbo.table.ID is a column that contains a bunch of values, all 7 digit integers that I'd like to match to one of the parameters in my Oracle query. So I should build my Oracle query SELECT in a string or my MSSQL SELECT in a string? Then run the openquery with EXEC or the MSSQL part? Just a little confused as to what you're referring to. – Dispersion Jan 07 '16 at 21:14
  • 1
    Can you create a VIEW in SqlServer with openquery and then use that view to achieve your results? – Abdul Hameed Jan 08 '16 at 00:03
  • @Dispersion It's a little more difficult when you have to get multiple specific values but you do have different options. one option is not to use an open query and use fully qualified names such as [OtherServerName].[OtherDB].[dbo].[OtherTable] joined with [ThisServerName].[ThisDB].[dbo].[thisTable]... (I'm not sure 100% of the syntax for accessing an Oracle Table but I know there was a peculiar way to access a MySQL table). – JohnG Jan 08 '16 at 13:24
  • @Dispersion The syntax to query an Oracle table through a linked server is as follows: [OtherServerName]..[dbo].[OtherTable]... I found this [here](http://stackoverflow.com/questions/11309762/querying-an-oracle-database-from-sql-server) – JohnG Jan 08 '16 at 13:30

1 Answers1

0

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

Dispersion
  • 11
  • 6