Page 1 of 1
Data insert multiple times in target database
Posted: Sun May 29, 2011 4:58 pm
by hargun
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.
Posted: Sun May 29, 2011 7:52 pm
by hargun
help me please
Posted: Sun May 29, 2011 8:48 pm
by prakashdasika
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...
Posted: Sun May 29, 2011 9:32 pm
by chulett
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.
Posted: Sun May 29, 2011 11:25 pm
by ray.wurlod
hargun wrote:help me please
Some of us prefer to have a life on the weekend.
Posted: Sun May 29, 2011 11:40 pm
by peddakkagari
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)
Posted: Mon May 30, 2011 9:44 am
by hargun
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
Posted: Mon May 30, 2011 9:49 am
by hargun
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...
Posted: Mon May 30, 2011 12:09 pm
by jyothisdasms
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.
Posted: Mon May 30, 2011 3:04 pm
by chulett
hargun wrote:hi craig,
Can you advice me please...
Already did.