topic Re: incremental insert or refreash in Talend Studio

incremental insert or refreash

modara — Fri, 15 Nov 2024 23:13:46 GMT

Hi,

I have MSSQL that has customer table and Primary key is Customer_ID, TYPE: string and DB Nvarchar

I have MYSQL that simple sync Column from MSSQL and PK is Customer_ID, TYPE: string and DB varchar

I want auto increment so when MSSQL have new data inserted then when I run MYSQL will have that new data only

Example: MSSQL have 7 data and MYSQL also have same 7 data

but

new 2 data inserted to MSSQL so when I run talend TOS MYSQL will only take those 2 data not whole 7+2 = 9 data

following is in MYSQL settings.

When I run I see evert time all data is inserting from MSSQL to MYSQL

AS my DB type is varchar and type is sting, is it an issue?

I dont want to use tMAP as its very simple form MSSQL to MYSQL data moving

please help

Re: incremental insert or refreash

gjeremy1617088143 — Wed, 09 Feb 2022 15:33:20 GMT

hi,You have to use a tmap and make an inner join between the two table then send data to a output of the tmap with catch inner join reject set to true.

Then you just have to use a tDBOutput with insert option only. also it will be more efficiency than update or insert

Re: incremental insert or refreash

modara — Wed, 09 Feb 2022 15:36:40 GMT

@guenneguez jeremy ,

As I said I just simple copy data from one data base to another

as you said use tmap and two table then which way I can use another sales table for an example to join inner?

thx

Re: incremental insert or refreash

gjeremy1617088143 — Wed, 09 Feb 2022 15:47:04 GMT

you have to do the inner join between the table of both bdd mssql and mysql,

tMap is made for that, you could also use tJoin component wich is lighter you have to check the inner join checkbox and use the reject link

Re: incremental insert or refreash

modara — Wed, 09 Feb 2022 15:51:50 GMT

Hi,

Sorry to ask you again.

for tMAP

i need from MSSQL then tMAP need (two table to join) then copy them to MYSQL

where I can have these two to join then one side then copy them to mysql as i have one table which is sale in mssql. for tmap i need 2 tables on left side then join them on right side then copy data to mysql

if i m not wrong. so please suggest me how i get another table to make tmap join

thanks

Re: incremental insert or refreash

gjeremy1617088143 — Wed, 09 Feb 2022 16:05:08 GMT

main mssql db table , lookup mysql db table(the table where you want to write, so use a tdbinput with only a select of the id)

output mysql db table , the purpose is to check in your mysql table if id exist before writing anything inside then only insert id wich won't exist in your mysql table. much faster than update or insert

Re: incremental insert or refreash

modara — Thu, 10 Feb 2022 03:35:42 GMT

would you please help me how to insert id column or how i can make it done. sorry as i said i new in this field so please help me

Re: incremental insert or refreash

gjeremy1617088143 — Thu, 10 Feb 2022 08:36:27 GMT

something like this :

Re: incremental insert or refreash

modara — Thu, 10 Feb 2022 09:14:57 GMT

so i have two target table one for join and other for out come

Re: incremental insert or refreash

gjeremy1617088143 — Thu, 10 Feb 2022 09:26:36 GMT

it's the same, but it just you control before with a select in that your id are not in before insert

if i follow your example : source table have 7 data and target 5 , so you join them with tmap you catch the reject here 2 then you insert in the same table the 2 reject and finally you have 7 in source and target