how to do an insert/update with foreign keys rejects (MSSqlServer2008)

[Anonymous]

HI,
I would like to know how to implement efficiently an insert/update data from table source to table target,
with rejects (foreign keys errors) in an error table
something like
source ---insert/updat---> target ----> fk rejects
source table, target table, rejects table are under same sgbd MS SqlServer 2008 R2
i have tried things like :
1/ tMSSqlInput_1 (source) --main--> tMap --main--> tMSSqlOutput_1 (cible)
2/ tMSSqlInput_1 (source) --main--> tMSSqlOutput_1 (cible)

in both cases i have tried :
1/ option "insert", "update", "insert or update", "update or insert"
2/ with or without a link to reject table :
tMSSqlOutput_1 (cible) ---reject---> tMSSqlOutput_2 (reject)

I would like to have something like merge sql order but with fks rejects
merge Contract_2 as target
using DSA_Contract_2 as source
on ( target.Contract_Code = source.Contract_Code )
when matched then
update set
target.Contract_Code = isnull( source.Contract_Code, 'UNKNOWN' ),
target.Contract_Ref = source.Contract_Ref,
target.Contract_Type_Code = source.Contract_Type_Code,
target.Commodity_Code = case when source.Commodity_Code is null or ltrim(rtrim(source.Commodity_Code)) = '' then 'UNKNOWN'
else ltrim(rtrim(source.Commodity_Code))
end,
/* ...etc ... */
when not matched then
insert (
Contract_Code,
Contract_Ref,
Contract_Type_Code,
Commodity_Code,
/* ... etc... */
)
values (
isnull( source.Contract_Code, 'UNKNOWN' ),
source.Contract_Ref,
source.Contract_Type_Code,
case when source.Commodity_Code is null or ltrim(rtrim(source.Commodity_Code)) = '' then 'UNKNOWN'
else ltrim( rtrim( source.Commodity_Code ) )
end
/* ....etc.... */
)
OUTPUT deleted.*, $action
;

[willm1]

In your tmap, bring in your two datasets (source and target) and join on target.Contract_Code = source.Contract_Code
Create one output with the fields you want from both tables; call this 'matched'; you will use this 'matched' row to do an update on your table. When you place your database output component (say tOracleOutput), set the data operation to 'update'. Don't forget to set the primary key for the row in the tMap component.
Then create another output in tMap called 'unmatched' or 'rejects' and select the fields; set this to 'inner join reject = true'; this will get all records that failed the match. You can then insert these into some other table.

[Anonymous]

thanks for this start point, but could you give me more details ?
as i understand :
tMSSqlInput_1 (source) -main-> tMap_1
tMSSqlInput_2 (target) -lookup-> tMap_1

tMap_1 --main (order:1)--> tMSSqlOutput_1 ("matched") with action on data = "update"
but after, i understand you reject data doesn't match ..
what i want is :
( insert if not already exist / update if already exists ) and catch rejects such as foreign key errors from database

[willm1]

Ok... Just wondering - doesn't the 'Rejects' flow from the database do it for you? See attached...

[Anonymous]

I try two things below :
source data (8000 rows)
on each try i prepare 2 cases :
- all data ok to be insert in target table
- all data ko to be rejected and all insert in reject table

try 1 :
source ---> tMap
ref tables --> tMap

tMap (when filter ok) ---> target table
tMap (when filter ko) ---> reject table
(reject table without fk)

when all source data are rejected in reject table : job duration is a few secondes (50secondes) and all row in reject table
when all source data are inserted in target table : job duration 3 minutes and all row in target table


try 2 :
something as you suggest :
source --main--> target ---rejects---> reject table
even source data is ok or not : near 10 minutes to have result
if all data ok -> target table
if all data ko -> reject table

regards

[willm1]

So it sounds like your job is running, albeit running slow... You could configure your tMap to store lookup data in files to speed up performance.
You could also tweak your java heap size for the job, and/or your Studio. See http://goo.gl/3X2jSh