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 .
asked Jul 05, 2016 at 03:31 PM in Default
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.
Hope that helps!
answered Jul 11, 2016 at 02:56 PM
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
answered Jul 14, 2016 at 10:08 AM
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!
answered Jul 15, 2016 at 03:51 PM