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.

Thirteen 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
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
Thirteen Stars

Re: Update and Insert the records in the existing table

Still do you have this issue?
Manohar B