topic Re: Connect two tables from different DBs in Talend Studio

Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 12:01:19 GMT

Hello!

I want to convert this query in a talend job and store it in a table. Currently both tables are in DB1. But RESOURCE table had moved from DB1 to DB2.

I have query like:

with filterLength as
(select distinct len(Filter_Element_ID) Len_filter
from DB1.CALCULATION where len(Filter_Element_ID) is not null and System = 'A')
select s.PROJECT_ID
, r.Calculation_ID
, s.ACTIVITY_RUB4
, ACTIVITY_RUB5
, r.Filter_Element_ID
, r.Filter_B_TYPE
, f.Len_Filter
, SUBSTRING( ACTIVITY_RUB4, 1, f.Len_Filter)
from DB1.RESOURCE s
join filterLength f on 1=1
join DB1.CALCULATION r
on SUBSTRING( ACTIVITY_RUB4, 1, f.Len_Filter) = r.Filter_Element_ID
and f.Len_filter = len(r.Filter_Element_ID)
and System = 'A'
and (Filter_B_TYPE = s.ACTIVITY_RUB5 or Filter_B_TYPE is null)

I created two DBInputs and know I have to use tMap. But unable to implement the above logic. Need help in understanding how to do it in Talend.

Regards

Priyadarshini

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 13:18:29 GMT

As I can see on your description, you have one SQL Request that picks information from two databases.

It's probably means, you have a DB_LINK between the two databases and so, from one DB, you can launch the sql request from a database I will call DB1.

1. You open connection to the database you will use to launch the resquest
2. tDBInput to launch the sql
3. tMap to adapt the output from sql request to your final table shema
4. tDBOutput to insert the data in the final table

I hope this will help

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 13:22:31 GMT

Currently both tables are in DB1 but RESOURCE table has moved from DB1 to DB2.

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 13:55:11 GMT

And the DBs lies on same server.

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 15:24:47 GMT

@priyadarshiniv

Thanks for contacting through message for this query but I could see that @dgm01 has already provided the answers.

The most easy way is to use a DB link so that you can retain the original SQL. Instead of table name, you will have to use tablename@DBlink to make things work.

Please refer the below links to understand more about this concept.

https://stackoverflow.com/questions/28368876/query-table-from-another-oracle-database

https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 15:50:08 GMT

Thank you for your reply. I am using SQL database. Can I use DB link ?

Regards
Priyadarshini

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 16:28:44 GMT

what do you think about this?

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017

Re: Connect two tables from different DBs

Anonymous — Fri, 24 May 2019 16:31:10 GMT

I hope this will help