Insert distinct records from duplicate valued records in DATASTAGE

Question

Hi I am using DATASTAGE to import hive data into Oracle, I don't have any primary key constraints in hive but on Oracle I am having a combination primary key. For example I have a data which doesn't have duplicates on basis of whole record but the pk constraints has duplicate Table has columns Table name: item_details ;--(hive) no primary key constraints

Id mfg_date item exp_date

1 12-01-2018 abc 31-03-2018

2 12-01-2018 cde 28-02-2018

3 15-01-2018 efg 10-04-2018

4 12-01-2018 abc 10-04-2018

Where the mfg_date and item together are primary key for the target table(Oracle) which is same structure.

And I need to push the data into target table.

But it says a primary key violation and gets aborted.

Can anybody give me a solution.

Ps. We cannot change the schema for the tables

Well there is a remove duplicate stage in DataStage - so it is possible — MichaelTiefenbacher
– MichaelTiefenbacher, Commented Feb 15, 2020 at 9:24

arcee123 · Accepted Answer · 2020-05-30 05:09:48Z

0

This is what I use [[twt]] for. it's faster than doing Lookup and join stages.

start by changing the output sql from Insert to Custom SQL.

then you can create a custom SQL Statement like the one below:

insert into <<target table>>
select Id, mfg_date, item, exp_date from [[twt]]
where not exist (select 1 from <<target table>> where <<target table>>.id = [[twt]].id)

it'll fire off records that don't exist. because custom sql allows for multiple statements, you can do your whole update this way.

By using [[twt]], you can change this to an ELT form of loading, and control the inserts.

thanks.

answered May 30, 2020 at 5:09

arcee123

30514 gold badges61 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert distinct records from duplicate valued records in DATASTAGE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related