Update and Insert the records in the existing table

Six Stars

Update and Insert the records in the existing table

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.


Accepted Solutions
Six Stars

Re: Update and Insert the records in the existing table

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.

 


Regards,
Mohit

All Replies
Six Stars

Re: Update and Insert the records in the existing table

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

please mention update key.

Six Stars

Re: Update and Insert the records in the existing table

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.

 


Regards,
Mohit
Six Stars

Re: Update and Insert the records in the existing table

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 :

edecadac

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

existing row:

PDll_idacsddsedecadaccreated_on
APR-18s1221111118002001000101010

4/5/2018 12:39

updated row :

PDll_idacsddsedecadaccreated_on
APR-18s12211111180020020000424204/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,

Six Stars

Re: Update and Insert the records in the existing table

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:

edecadac

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

the first record is as below now: (existing)

 

PDll_idacsddsedecadaccreated_on
APR-18s12211111180020010001010104/5/2018 12:39

if i get the Updated record as below:

PDll_idacsddsedecadaccreated_on
APR-18s12211111180020030004455204/5/2018 12:39

 

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

 

Please do needful.

 

Thanks.

Forteen Stars

Re: Update and Insert the records in the existing table

,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.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Six Stars

Re: Update and Insert the records in the existing table

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.

 


Regards,
Mohit
Forteen Stars

Re: Update and Insert the records in the existing table

Still do you have this issue?
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Update and Insert the records in the existing table

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;

Warm Regards,
Please don't forget to give Kudos if it resolves issue, and if you think its Apt you can also mark its as solution.
Eight Stars

Re: Update and Insert the records in the existing table

Inserts-Updates-performanceimprovement.PNG

Warm Regards,
Please don't forget to give Kudos if it resolves issue, and if you think its Apt you can also mark its as solution.
Eight Stars

Re: Update and Insert the records in the existing table

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...

 

 

Warm Regards,
Please don't forget to give Kudos if it resolves issue, and if you think its Apt you can also mark its as solution.

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads