DSXchange

Duplicate row error in <DB_NM>.<TBL_NM>. Teradata DB.
------------------------------------------------------

RowGen -----> TeradataPX Stage

There is an INSERT sql in the Teradata PX stage. There are a series of insertions happening on the target tbl in this job.

Insert into #TBL_NM_2# ( Select * from #TBL_NM_1# where #COL_NM# = 'value');

Job run 1
1st set of data to be loaded:
----------------
1
2
3

Job successful.


Job run 2 - with a different set of params.
2nd set of data to be loaded:
----------------
3
4
5
6

Job ends with a warning. Error msg: Duplicate row error in <DB_NM>.<TBL_NM>

Obviously this is because of the duplicate row with value '3'. Problem is due to this even the other rows with data '4','5' & '6' are getting dropped.

How can I get around this problem with the same job design?

I tried with the following job design, with the same sql divided among the 2 stages:


TeradataPX Stage 1 -----------------> TeradataPX Stage 2

( Select * from
#TBL_NM_1# where --------------> Insert into #TBL_NM_2# ....
#COL_NM# = 'value');



This time the desired result was obtained. i.e. row 3 was dropped and the other new rows in the 2nd set of data were loaded into the target tbl.
I couldnt understand the difference b/w the 2 approaches.

Can anybody please throw some lights here.

Thanks,

If you've set "All rows = one transaction" then you would expect rows for 4, 5 and 6 to be rolled back (not quite the same as dropped, but the same effect). Use one row per transaction to cure that particular issue.