Problem while Bulk insertion of records
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
Problem while Bulk insertion of records
Hi Everyone,
In one of my jobs i am inserting or updating the records on the basis of some conditions.In order that duplicate records should not be going to the target table again and again and generating fatal errors.I am using the "Update then Insert" Upsert Mode.It is working fine when the no of records is less.But if the no of records is very high that means when i am doing a bulk load then in case of that its again logging fatal erorrs in the job log if it is getting some duplicate rows for insertion.Can anyone please guide me on this.
Thanks in advance,
In one of my jobs i am inserting or updating the records on the basis of some conditions.In order that duplicate records should not be going to the target table again and again and generating fatal errors.I am using the "Update then Insert" Upsert Mode.It is working fine when the no of records is less.But if the no of records is very high that means when i am doing a bulk load then in case of that its again logging fatal erorrs in the job log if it is getting some duplicate rows for insertion.Can anyone please guide me on this.
Thanks in advance,
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
did you check the INSERT sql and UPDATE sql code that was generated and if that has correct key settings??
Update then Insert--Upsert mode-- will only try to update the record if it finds the duplicate key combination in the table otherwise it will happily tries to insert the record!!
Update then Insert--Upsert mode-- will only try to update the record if it finds the duplicate key combination in the table otherwise it will happily tries to insert the record!!
thank you
- prasad
- prasad
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
1. Did you take care of properly assigning key columns in the DataStage?
2.Also, are you sure that you don't have any duplicates coming in from source? It could be possible that you are trying to insert a new row twice since it is coming from the source. Try using a Remove Duplicates Stage on the key column before you load data into the table. I had a same issue here at my client site and that is how it was taken care of. It might be possible in your case too.
2.Also, are you sure that you don't have any duplicates coming in from source? It could be possible that you are trying to insert a new row twice since it is coming from the source. Try using a Remove Duplicates Stage on the key column before you load data into the table. I had a same issue here at my client site and that is how it was taken care of. It might be possible in your case too.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
it could be that you need to modify your query a bit ..punar_deep wrote:Yes they are part of the primary key and i have handled the same properly in my query itself.
lets say your primary key is c1+c2+c3 and your unique index has c2 in its definition:
with the logic you have, DS will check if c1+c2+c3 in the incoming feed already exists in table if it is then updates the table otherwise inserts (or tries to) to table .. on the other hand its completely possible that c1+c2+c3 might be unique when compared to table values but c2 might already be there in table .. and this case will throw an ERROR!!
thank you
- prasad
- prasad
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
-
- Premium Member
- Posts: 397
- Joined: Wed Apr 12, 2006 2:28 pm
- Location: Tennesse
Hi,
You need to check with the key columns only. You may not have duplicates, but the key column makes this problem. Better to check with other columns too.
You need to check with the key columns only. You may not have duplicates, but the key column makes this problem. Better to check with other columns too.
1) I am taking proper care while defining the key columns.
2) In my case before inserting the records into the target table i am passing the them through a sort stage in which i am not allowing any duplicates.So, the chances of having two records in the same input load are not there.
-
- Participant
- Posts: 19
- Joined: Sun Aug 21, 2005 5:44 am
Good!!punar_deep wrote:Hi Prasad,
You are right on your point.The problem was very much what you stated.I was able to test the same with the inputs that you gave.Thanx a lot for the valuable help that you have provided.
Thanks alot.
If you still want the update happen when c2 matches .. you can update the where clause of UPDATE sql to include this case as
WHERE
(
(c1=c1 and c2=c2 and c3=3 .. the same as wht you might have already)
OR
(c2=c2)
)
thank you
- prasad
- prasad