topic Re: How to insert into many DB tables at once? in Talend Studio

How to insert into many DB tables at once?

Anonymous — Sat, 16 Nov 2024 14:14:13 GMT

Tags:
Hi all.
I have to read a file Excel and for each record, i need to write on many database tables.
I tried to use tFileInput and tDBOutput but i found i can write only on *one* table. How can i perform many operations for each row?
By the way, operations for a single row must be in a transaction: if something goes wrong, they have to rollback. Also this, i do not know how to do.
Someone may suggest me some hint?
Thank you

Re: How to insert into many DB tables at once?

Anonymous — Tue, 09 Sep 2008 19:12:45 GMT

What database are you using?
For most databases, you have the following types of components:
tConnection
tRow
tCommit
tRollback
The tRow component allows you to execute ONE SQL command for the current row in your input stream (in your case a row from the Excel spreadsheet).
If your right click on most components, under trigger, you'll see OnComponentOK and OnComponentError. So, you would use the Rollback if there was an error.
Perhaps someone else could post a more detailed example - my time is short today.

Re: How to insert into many DB tables at once?

Anonymous — Tue, 09 Sep 2008 21:22:31 GMT

Hi,
depending on your needs you can do:
a) more than on tRow/Output behind another
b) split your flow with tReplicate (same metadata)
c) use a tMap to split your flow (different metadata)
Bye
Volker

Re: How to insert into many DB tables at once?

Anonymous — Wed, 10 Sep 2008 17:23:17 GMT

Hi,
I have the same need: I read an Excel spreadssheet and I need to insert in 3 tables of my PostgreSQL database.
In tableA, I use some columns to insert a row, as this is an auto increment column (SERIAL) I get a new id value = idA.
In tableB, I use some other columns, plus idA to insert a row, as this is an auto increment column (SERIAL) I get a new value id = idB.
In table C, I use idA and idB to insert a new row !
I have created a few others jobs before, to insert rows on tables with SERIAL columns from Excel ... but so far, I have never needed to re-use the value of the id, so I don't know how to do this.

Thanks in advance for any help,
L.

Re: How to insert into many DB tables at once?

Anonymous — Wed, 10 Sep 2008 20:46:52 GMT

Hi,
sorry I haven't had this problem . So I couldn't give you a solution. But have you tried to use tlLastInsertId ?
This could solve your problem. This component is very new. So, depending on your version, not available.
Bye
Volker

Re: How to insert into many DB tables at once?

Anonymous — Thu, 11 Sep 2008 19:48:15 GMT

Thank you all for your suggestion, i am still working on them. I am yet a newbie on talend.
iroche i have to do something very similar to your example: for every row from Excel file, i have to generate a keycode which will be inserted in new records on two tables: one containing main info and the other containing additional info.
So, to better explain, i have a single row to be splitted into two tables, *whose column names are different from excel ones*, and some of the field in tables are filled with extra infos (i.e constants, keycode, and evaluated expression ).
By the way, how do i manage to map excel fields into DB table fields when names are different? i tried with a tMap component but cannot get it.
Database is MySQL.
thank you

Re: How to insert into many DB tables at once?

Anonymous — Thu, 11 Sep 2008 20:28:35 GMT

Hi,
one idea for your foreign key problem: Can you read the table after the insert again?
For mapping Excel to you database: tMap is the right one. The best way is to define your Excel-metadata, than connect to your tMap, define you database (with metadata) and as *last* step map tMap to your database. TOS will ask you if you would like to use the metadata for tMap. Say yes and do your mapping.
Bye
Volker

Re: How to insert into many DB tables at once?

Anonymous — Fri, 12 Sep 2008 08:57:34 GMT

Ok, i will try, thank you.

Re: How to insert into many DB tables at once?

Anonymous — Fri, 12 Sep 2008 11:57:48 GMT

Hi,
depending on your needs you can do:
a) more than on tRow/Output behind another
b) split your flow with tReplicate (same metadata)
c) use a tMap to split your flow (different metadata)
Bye
Volker

Thank you Volker, it works! I followed exaclty the order you suggested, and now tMap propose me the rigth DB colum names.
In my previous trial, tMap proposed me the file schema for both input and output mapping
To be honest, i do not know why previous map was wrong.

Re: How to insert into many DB tables at once?

Anonymous — Fri, 12 Sep 2008 21:44:47 GMT

The logic how (or when) TOS adopt the metadata of the previous or next component is sometimes confusing (for me).
But this is one of the rules i learned in the time I use TOS. First the database, then the tMap. 🙂
Bye
Volker

Re: How to insert into many DB tables at once?

Anonymous — Thu, 24 Sep 2009 09:10:13 GMT

hi
I have a excel sheet as the input source and Mysql db tale as output, i can insert data from
excel sheet to table. but i want another thing to do, i want to add another new row for each
rows in the table
how can i do this

Re: How to insert into many DB tables at once?

Anonymous — Fri, 25 Sep 2009 07:35:51 GMT

Hello ishk

but i want another thing to do, i want to add another new row for each
rows in the table

Can give us example to explain your request? I will show you a job.
Best regards

shong