topic Re: insert/Update data into DB in Talend Studio

insert/Update data into DB

Anonymous — Sat, 16 Nov 2024 02:06:05 GMT

I am new to talend world and trying learn the flow of data.

I have a customer csv file which i need to load into a postgresql db. I could have done this with tdboutput component but wanted to do this using insert query.

So I am using 2 components.

tfileinputdelimited -> tdbrow.

I used this query in my tdbrow component:

"INSERT INTO customers (cust_id, cust_name, cust_lname, cust_email, cust_city, cust_type) values
(row1.Cust_ID, row1.Cust_Fname, row1.Cust_Lname, row1.Cust_Email, row1.Cust_City, row1.Cust_Type)"

This is the error I am getting:

Exception in component tDBRow_1 (insertjb)
org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "row1"

Thanks in advance.

Re: insert/Update data into DB

manodwhb — Mon, 22 Jun 2020 06:03:15 GMT

@bunty_rsr ,you need to design the job below way and the key should be a check on cust_id. in the schema of tdbioutput.

Re: insert/Update data into DB

Anonymous — Mon, 22 Jun 2020 10:00:09 GMT

@manodwhb Thank you. We can do it this way.

But is there a way that we can use sql query as I asked in my original post using tdbrow component?

Re: insert/Update data into DB

David_Beaty — Mon, 22 Jun 2020 11:08:18 GMT

Hi,

You can, but it would be incredibly inefficient. you need to structure the job something like:

tFileInput Delimited -> tFlowToIterate -> tDBRow

Your tDBRow component will need to reference the globalMap variables create between the file input and flow to iterate.