Data insert multiple times in target database

hargun · Post by **hargun** » Sun May 29, 2011 4:58 pm

Hi,

I have a question like in my job design .Its a very simple job i am just extract the data from sequential file and load it into target database with same hard code values and the job is run fine but the problem is whenever i run the same job 2 or3 times again the same data insert in 2 and 3 times in target database means duplicates of data inserted again and again For instance if i insert 1,2,3 and rerun the job again it will again insert 1,2,3 mean data is insert multiple times so is there any way by which data should be insert one time only once if i run the same job again and again.
Please somebody help me on this problem.

hargun · Post by **hargun** » Sun May 29, 2011 7:52 pm

help me please

prakashdasika · Post by **prakashdasika** » Sun May 29, 2011 8:48 pm

Be more specific with your question. What database it is? Which stage are you using? What table are you loading to i.e what constraints and indexes does it have? what method are you using to load i.e bulk load or other etc...

chulett · Post by **chulett** » Sun May 29, 2011 9:32 pm

If you want to do insert only, use a lookup to determine if the data already exists and then only insert those that do not. That or take the "insert else update" approach but you'll need a unique contraint to allow that to work.

ray.wurlod · Post by **ray.wurlod** » Sun May 29, 2011 11:25 pm

hargun wrote:help me please

Some of us prefer to have a life on the weekend.

peddakkagari · Post by **peddakkagari** » Sun May 29, 2011 11:40 pm

First you define the keys in the target stage based on your requirement then
if your database is DB2 then use load type as UPSERT(UPDATE+INSERT), it will insert the record if it is new else it will update.

If your databse is Oracle use "insert else update" approach as suggested by chulett.

Please let us know your target databse and the target columns(both keys and non keys)

hargun · Post by **hargun** » Mon May 30, 2011 9:44 am

hi,

My target database is DB2 .Here is my job design.

Code: Select all

Seqfile........>
                       MERGESTAGE......>XFM...........>  DB2 DATABASE
ODBC .........>

IN TARGET DATABASE I AM DOING ONLY INSERT OPERATION AND AFTER RUNNING JOB AGAIN SAME DATA INSERT AGAIN.

IS IT THIS ONE HAPPENS DUE TO MERGE STAGE OR I HAVE TO USE LOOK UP STAGE .

Reply me please and thanks for quick reponses

hargun · Post by **hargun** » Mon May 30, 2011 9:49 am

hi craig,

i want the unique record not the duplicates of record in target DB2 database.

i am doing only insert opeartion and try with other insert+update operation too giving me the same result.

Can you advice me please...

jyothisdasms · Post by **jyothisdasms** » Mon May 30, 2011 12:09 pm

Create a lookup with the target database and insert the records only if the key values are not present in the target.Pull out the reject link for this.

chulett · Post by **chulett** » Mon May 30, 2011 3:04 pm

hargun wrote:hi craig,

Can you advice me please...

Already did.