how to insert unique records

[jpraveen]

Hi

my reqiurement is to insert the records which are not in the Target(having Cluster Primary Key on Target table).i had 3 jobs and Target is same table,the first job will insert the distinct records(the job finished successful)while the Second job is aborted due to PK violation.

so i need to take the target table in to LOOKUP,but how the logic should be implemented that only the records which are not available in the target should be INSERTED
can u suggest some logic.

Thanks in advance

[anbu]

Use lookup stage and use reject link from lookup stage to get the records which are not in Target table

[austin_316]

If the jobs are not executed in parallel you can take a lookup of this table and fetch the data from the table using query something like

Code: Select all

select pk1,1 as dummy from table

and forward dummy from lookup stage.
and then after the lookup you can use a transfomer or filter.
if you are using the transformer then in the link constraint give the condition as

Code: Select all

IsNULL(link.dummy) 

where insert link goes.
or if you are using filter then you can give

Code: Select all

dummy<>1

please try this and i hope it would solve your issue.

[jpraveen]

Hi

i am running the job parallel and i want to insert the records which are not in the target table(not to use dataset we have only target table),so i use Lookup and my target as a reject link and Primary output as Peek stage.so is there any option to insert the reject records from the look up.

[anbu]

Insert the records from reject link in to the target table