May 14, 2009 at 12:46 pm
Hi,
I had table A in oracle and Table B in SQL Server 2000. Now, I had to extract data for a report and want to make a inner join between Table A and Table B.
Is it possible? If so, can some one post a sample of query.
Thanks in Adv.
SQL DBA.
May 14, 2009 at 12:56 pm
Linked servers would be an obvious solution. I believe the provider would be MSDAORA. Never linked to Oracle before, only other SQL server, but I know it works. Check BOL or Google it or even search on this site.
-- You can't be late until you show up.
May 14, 2009 at 1:09 pm
I know how to extract data from Oracle using MSDAORA or OPENQUERY but never made any joins between both data set.
And really don't know how to do that.
SQL DBA.
May 14, 2009 at 2:16 pm
SanjayAttray (5/14/2009)
I know how to extract data from Oracle using MSDAORA or OPENQUERY but never made any joins between both data set.And really don't know how to do that.
there are two options
1)You can fetch the information from open query to a temp table and then from there you can go.
2) You can directly join local table to oracle by using four point joint
SELECT oracle.*,SQL.*
FROM OrclDB..MARY.SALES as oracle inner join database.dbo.sales SQL
inner join Oracle.ColumnName= SQL.columnName
I would suggest to make use of option 1, as it fetches required data initially into a temp table and then get the actual required result selt by joining temp table with local table.
Option 2 can be used only if remote table on oracle is pretty small.
May 14, 2009 at 2:40 pm
SELECT oracle.*,SQL.*
FROM OrclDB..MARY.SALES as oracle inner join database.dbo.sales SQL
inner join Oracle.ColumnName= SQL.columnName
No. Above example doesn't work. Option 1 won't work for me as Oracle.Table1 had 39 million records.
SQL DBA.
May 14, 2009 at 2:52 pm
Can't you filter and fetch data from oracle through open query and then join required data or do you want to do join on entire data with out filtering?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply