pbs & questions about "insert/update" / "ELT components"

One Star

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) :
set . = .,
. = .
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 ?
Seventeen Stars

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

Re: pbs & questions about "insert/update" / "ELT components"

about job 3 :
you can see screen copy of job and screen copy of error