topic Re: Update and Insert the records in the existing table in Talend Studio

Update and Insert the records in the existing table

Anonymous — Mon, 23 Apr 2018 13:42:03 GMT

Hi,

As i have the existing table , in which i need to perform the update and insert option.

It means i need to insert new records and also update the existing records.

Please suggest me

Thanks.

Re: Update and Insert the records in the existing table

Anonymous — Mon, 23 Apr 2018 14:25:51 GMT

if you are using MSSQL, then use tmssqloutput, there is a option insert or update.

please mention update key.

Re: Update and Insert the records in the existing table

mks02 — Tue, 24 Apr 2018 07:51:07 GMT

Hi,

Using tMSSQLOutput component (any DB output component), there are options to perform these actions:

Property Name: "Action On Data"
- Insert or Update: First it looks for Insert a record. If the record for given reference already exists, it will update that record.
- Update or Insert: First it try to update the record for given reference. If record does not exist, then new record would be inserted for that table.

You can choose any of the options as per your ease.

Re: Update and Insert the records in the existing table

Anonymous — Wed, 25 Apr 2018 07:41:01 GMT

Hi,
Thanks for your suggestion.
I tried changing the Property in tMySQLOutput component.
I don't have any primary keys in my table.

The excel is attached in the below: In that if you observe the first two records are same except the

columns :

i have a requirement like if the first record given below is updated:

existing row:

PD	l	l_id	ac	sd	ds	ed	ec	ad	ac	created_on
APR-18	s1	22	11111	1800	200	100	0	10101	0	4/5/2018 12:39

updated row :

PD	l	l_id	ac	sd	ds	ed	ec	ad	ac	created_on
APR-18	s1	22	11111	1800	200	2000	0	4242	0	4/5/2018 12:39

so if the updated record comes in to table then existing should be replaced by the updated record.

i tried with the Action on data keeping as "Insert or Update' and "Update or insert" Property.

I am using the Talend Open studio.

Please do needful.

Thanks,

DUMMY.xls

Re: Update and Insert the records in the existing table

Anonymous — Wed, 25 Apr 2018 08:13:31 GMT

Hi,

Thanks for the suggestion.

I have the output Component tmysqloutput in the talend.

I tried using the same Action on Data as "Insert or Update" and "Update or Insert", still its not working.

First thing is I dont have any Primary keys in my table and data is attached in the excel sheet in the below.

I will get updates like i wont get any changes in the columns except the below columns:

suppose if you notice the first two records in the excel sheet.

the first record is as below now: (existing)

PD	l	l_id	ac	sd	ds	ed	ec	ad	ac	created_on
APR-18	s1	22	11111	1800	200	100	0	10101	0	4/5/2018 12:39

if i get the Updated record as below:

PD	l	l_id	ac	sd	ds	ed	ec	ad	ac	created_on
APR-18	s1	22	11111	1800	200	300	0	44552	0	4/5/2018 12:39

i want the existing record to be replaced by new record.

Please do needful.

Thanks.

DUMMY.xls

Re: Update and Insert the records in the existing table

manodwhb — Fri, 27 Apr 2018 07:42:00 GMT

,as per the input excel you can define key onPD column in tmap and you can do Insert or Update or Update or Insert in tmysqloutput.

Re: Update and Insert the records in the existing table

mks02 — Wed, 02 May 2018 09:46:26 GMT

Hi @Injarapu_1995,
You need to define a key either in tmap or in tmysqloutput component by clicking edit schema.

It is necessary to specify at least one column as a primary key while doing update operation.

Re: Update and Insert the records in the existing table

manodwhb — Thu, 10 May 2018 17:02:45 GMT

Still do you have this issue?

Re: Update and Insert the records in the existing table

Anonymous — Thu, 07 Feb 2019 18:23:52 GMT

I have good solution for insert and updates.
(With this solution you can perform inserts and updates very very quickly)
When you are getting records from the source from any data base, use tmap after any inputDb and do look up with the target , and use inner join lookup model with the cloumn to be matched, Then whatever it matches with key store all records in Temp table, Create table in the job run time.The other new which does not match with the key will go to direct insert.
Than using Merge concept sync temp table and target
After that delete temp table at the end
Example of Merge query (By using this merge query it will sync source and target with in seconds)

MERGE Table A AS TARGET
USING Table B AS SOURCE
ON TARGET.tableacol1= SOURCE.col1
WHEN MATCHED
THEN
UPDATE
SET

TARGET.col2= SOURCE.col2,
TARGET.col3=SOURCE.col3;

Inserts-Updates-performanceimprovement.PNG

Re: Update and Insert the records in the existing table

Anonymous — Thu, 07 Feb 2019 18:25:05 GMT

Re: Update and Insert the records in the existing table

Anonymous — Sat, 09 Feb 2019 20:22:21 GMT

If you have source table already created , then if you doing sync with target you can use ELT component, it can update or insert

https://help.talend.com/reader/iYcvdknuprDzYycT3WRU8w/X2VBExDJD3rEIaYGZOp0Fw

If you are performing anything by extracting source like applying any rules to transform data and than load, at that point you need to create a temporary table and make that temporary table as source and than do synchronizing which merging by using that above ELT componentm that ELT will work most types of databases,

or else you can follow this link below for the solution

https://community.talend.com/t5/Design-and-Development/Update-and-Insert-the-records-in-the-existing-table/m-p/150331#M91915

Re: Update and Insert the records in the existing table

Anonymous — Wed, 28 Aug 2019 14:20:11 GMT

Similar issue with Excel files.

I have a job which brings in an Excel Spreadsheet. The job was set up and works great.

Since then I received another Excel spreadsheet but a new field has been added and needs to be brought into the output.

Seems I cannot find a way(unless deleting previous) to sync up the new file for the process? Is there a way or does it always need for someone to DELETE the Input and recreate it from scratch?

Re: Update and Insert the records in the existing table

modara — Mon, 29 Mar 2021 08:21:13 GMT

Dear Concern,

I have one MSSQL table to take to MYSQL.

I set Primary key

As I have one table so i dont use tMAP

in action table: create table , action data: insert

then i insert 12 data from mssql to mysql

then change

in action table: default , action data: insert or update

then i insert 5 data in mssql but when i click RUN mysql takes all 17 data

not just the newly inserted data into mssql

your help is much appriciable

thanks