Update and Insert the records in the existing table

[Anonymous]

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.

[Anonymous]

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

please mention update key.

[mks02]

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.

[Anonymous]

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 :

ed

ec

ad

ac

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

[Anonymous]

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:

ed

ec

ad

ac

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

[manodwhb]

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

[mks02 · Accepted solution]

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.

[manodwhb]

Still do you have this issue?

[Anonymous]

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

[Anonymous]