pbs & questions about "insert/update" / "ELT components"
I want to do an insert/update in same db SQL Server between source and target tables source (DSA_Counterparty) -> target (Counterparty) - on a key column (pk) - if no exist in target then insert - if exist then update source : 45000 lignes
job 1 : tMSSqlInput --> tMap ---> tMSSqlOutput
if on tMSSqlOutput I choose option "insert" then it is ok : few secondes if target table is empty and error on pk if not (for data already exists) but if i choose any other option then it takes 40 minutes
job 2 - for "update" operation : component tMSSqlRow with a simple "update from" (because i didn't find a component to generate this order) : "update set . = ., ... . = . from where . = . " - for "insert" operation : components ELT for MSSqlServer to generate "insert" with : -- in tELTSqltMap filter below " 0 = ( select count(1) from Counterparty c2 where c2.Counterparty_Code = DSA_Counterparty.Counterparty_Code )"
this job 2 takes 1 seconde with either - source with 45000 rows and target empty - or source with 45000 rows and target with 40000 rows with updates to do
job 3 I have tried component tSQLTemplateMerge but even option i choose, it return a compilation error on "update" part
Problems and questions 1/ strange need to do an insert and update i can understand the tool generates insert and update (or merge) but i found strange ther is no component to parameter that in once time => is a component exist to do thaht in once ? 2/ pb with component tELTMSSqlMap : few times, I lost access to "settings" and need to recreate it => any idea ? 3/ copy/paste of this ELT components is random few times, after copy/paste from a job to anothe the tELTSqlMap pasted is empty : only tables in input/output but wiithout mapping and filters => so, i have recreated mapping and filters => any idea ? 4/ input/output links are ramdom to use it : few times, i didn't succeed to end or start a "OnSubjobOK" or "OnSubjobKO" from/to tELTSQLMap component => i have recreated components => any idea ?
Re: pbs & questions about "insert/update" / "ELT components"
to job 1: I would say you have a problem with missing indexes. Another reason is: if you choose insert (only) or update (only) the component enables the batch mode which reduces the network traffic drastically. You could read the target keys as lookup for a tMap and join it with your source. This way you could split the flow into a output flow for insert (inner join reject) or for update (inner join succeeded). A component what does insert and update at once does nothing else as execute a select and choose the next operation according to the result. This is exactly what the option "Insert and Update" does. to job 3: Please send us the compilation error.