1

I am trying to find out what options I have if I wanted to query Oracle and SQL Server data at the same time.

Here is the scenario:

Oracle database contains dataset A SQL Server database contains dataset B, C, D

I need to take dataset A and join it against B, C and D separately in order to generate certain results and counts.

Current solution:

  1. Query Oracle, export the data into SQL Server insert statements.
  2. In SQL Server Management Studio run the SQL Server insert statements that I generated from Oracle to insert data into a temp table
  3. Join the temp table with datasets B, C and D as needed

I'm looking for solutions that will cause the least amount of calls and workload to the databases.

Edit: I forgot to mention that linked servers or anything that changes server configuration are not allowed.

dtc
  • 10,136
  • 16
  • 78
  • 104
  • 1
    Maybe... http://stackoverflow.com/questions/352949/linq-across-multiple-databases – xQbert Dec 28 '11 at 01:12
  • 1
    Does it need to be a database only solution or with help of code? If the latter, what technology are you using(e.g. .NET)? In .NET you could use different connectionstrings to fill different datasets and join them via LINQ-To-DataSet. – Tim Schmelter Dec 28 '11 at 01:17
  • I'm using .net. So, your comment and the link xQbert gave me... I could query Oracle and store my data into a DataSetA, then query SQL server 3 times and store into DataSetB, DataSetC and DataSetD. Then use linq to join the datasets? Would that result in just 4 database calls and all the DataSet joining parts would happen in memory? – dtc Dec 28 '11 at 01:52
  • 1
    What amount of data are you talking about? Have you considered selecting from Oracle to a file, bulkcopying into SQL Server table and then joining that? You won't need many rows for that to be better than generating INSERT statements. –  Dec 28 '11 at 12:26
  • 1
    @metanaito: Yes, that was my idea. Ideally you would only need two queries(one on every dbms) and two datasets to load them into memory. The `join/group/sum`etc. would happen in memory. But xQbert suggested [LINQ-To-SQL](http://msdn.microsoft.com/en-us/library/bb386976.aspx) what is not the same as [LINQ-To-Datasets](http://msdn.microsoft.com/en-us/library/bb386977.aspx). – Tim Schmelter Dec 28 '11 at 13:00
  • @AndersUP : Unfortunately I can't make a table on the sql server that's why we are currently using the temp table. The number of records is in the thousands up to around 20,000. – dtc Dec 29 '11 at 02:01

1 Answers1

1

You can accomplish this by writing an MSSQL SSIS package. This would allow you to JOIN Oracle data with MSSQL data. SSIS packages are typically used for updating or moving data from one table/server to another, so the feasibility might depend on your underlying goals for this task/project.

Here's a short tutorial on creating an SSIS package. And here's a link to instructions on how to connect to an Oracle data source from within an SSIS package.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • +1 for SSIS. You can also make a linked server to Oracle, but it's slow. – SQLMason Dec 28 '11 at 21:46
  • I'm marking this as the answer since there are no more answers, but in my scenario we used LINQ and Datasets. – dtc Jan 24 '12 at 23:11