asked
how to join tables from two server
Hi , Please help me to write a sql query. I have connected to Oracle DB through SSIS "Microsoft OLEDB provider for Oracle" and Data Access Mode"SQL Commend" I have two tables: Table 1:XYZ (in Oracle DB) Table 2:Document_Number( in my local SQL Server) and I want the Query such as: Select * from xyz where xyz.Doc_Numb in (Select Doc_Numb from Document_Number). Please Help me the query . Regards, Kandan.M
Hi - I don't think you will get this achieved in one single shot, as there are two different database technologies involved. What I would advise you is to stage your Oracle table (XYZ) into SQL Server (i.e. bring all the data from there to here) and then use XYZ in SQL Server to do the join you wish to. To do the staging, you can use SSIS's "Data Flow task".
You should be able to do this directly with a MERGE JOIN within SSIS directly. You can specify a connection manager to Oracle & to SQL and then use the transform to join the two data sets. Note with a merge join, both will have to be sorted prior to the join itself. If the data set is large, you might want to consider what @nidheesh.r.pillai mentioned and bring the data you need into SQL and then manipulate it accordingly. Reference:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc150369-17d4-47d1-b012-94b765c2ace3/regarding-ssis-merge-oracle-and-sql-server-table?forum=sqlintegrationservices Hope that helps!
Hi nidheesh.r.pillai & John, Thanks for suggestions but my data set size is very large hence I am unable to complete by Merge join now I am Trying with linked server... Still I have not done working on it. Advise me if I am wrong Thanks, Kandna.m
Hi nidheesh.r.pillai & John, Thank you very much,I have got the solution through Merge Join Transformation. Also I need a clarification , When I am using Merge Join does it consume my local system memory. for Example( in my SQL table 5000 Document numbers and My oracle table contains 30L Rows of data when I am using Merge Join It will extract All 5000 rows from Sql table and 30L rows in Oracle table after Merge Join there are 5000 rows) So I want clarify does it consume my system memory for complete 30.05 L Rows or only for 5000 rows. Thanks for your Help!
18 People are following this question.
