Joining MS SqlServer data with Oracle data

OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed.

SqlServer
Group Name ID# Item Condition
AAA123 1 a 1
AAA123 2 a 1
AAA123 3 a 1
AAA123 4 a 2
AAA123 5 a 2
AAA123 1 b 3
AAA123 2 b 4
AAA123 3 b 3
AAA123 4 b 4
AAA123 5 b 3
BBB123 1 a 1
BBB123 2 a 1
BBB123 3 a 2
BBB123 4 a 2
BBB123 5 a 2

Oracle
Group Name ID# Value
AAA123 1 50%
AAA123 2 55%
AAA123 3 60%
AAA123 4 80%
AAA123 5 70%
BBB123 1 35%
BBB123 2 45%
BBB123 3 50%
BBB123 4 50%
BBB123 5 80%

I need to be able to get this:
Group Name Item Condition Value
AAA123 a 1 55%
AAA123 a 2 75%
AAA123 b 3 60%
AAA123 b 4 67.5%
BBB123 a 1 40%
BBB123 a 2 60%

Any idea how I can get the data from these two DBs to talk to each other? Thanks.

You should either import the data from Oracle into some temp table in SQL Server and work from SQL Server or the otherway. You need to choose the host first.

|||

That sounds great!

Can anyone help me understand how to import data from an Oracle DB into a temp table in SqlServer?

Thanks.

|||

Check out the DTS Import/Export wizard via Tools -> Data Transformation Services in Enterprise Manager.

|||

Unfortunately, I don't have enterprise... I tried using the linkedtable stored procedure but I don't have permissions on the Oracle Table, and I will not be granted them. Is there another way to import the data which does not require anything but SELECT priviledges (read-only account) where I can accomplish this using SqlServer Mgmt Studio Express? Thanks again.

-steve

|||

Check out OPENQUERY operator in books online.

|||

OPENQUERY operates on linked tables. Unfortunately, I cannot use this as a linked table because my account does not have permissions to access the sp_addlinkedtable stored procedure.

Does anyone know how I can do this without using linked tables? I can pull the data by connecting directly to the oracle DB (I have an account I can use SELECT with) but I need to use information stored in the SQL Server Table.

Is there some way I can use datatables in asp.net to do this, rather than transact SQL wihin the actual database? Thanks.