Problem while Bulk insertion of records

punar_deep · Post by **punar_deep** » Thu Oct 12, 2006 10:01 am

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,

samsuf2002 · Post by **samsuf2002** » Thu Oct 12, 2006 10:04 am

cud u plz let us know the job design and error log

ukyrvd · Post by **ukyrvd** » Thu Oct 12, 2006 10:05 am

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!!

meena · Post by **meena** » Thu Oct 12, 2006 10:09 am

Hi punar_deep,
Post the error you are getting.

punar_deep · Post by **punar_deep** » Thu Oct 12, 2006 10:11 am

Ya i have checked the same and what i am doing is that if the record already exists in the target table i am jus updating the key columns and if this is not the case then i am inserting the record.So i don't think there is any problem with SQL queries.

punar_deep · Post by **punar_deep** » Thu Oct 12, 2006 10:12 am

Hi,
I am getting the following error:

APT_CombinedOperatorController(1),2: [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Attempt to insert duplicate key row in object 'rep_fin_asset' with unique index 'rep_fin_asset_UIX1'

ukyrvd · Post by **ukyrvd** » Thu Oct 12, 2006 10:20 am

the unique key columns mentioned in 'rep_fin_asset_UIX1' index definition are part of the key columns in the query??

punar_deep · Post by **punar_deep** » Thu Oct 12, 2006 10:25 am

Yes they are part of the primary key and i have handled the same properly in my query itself.

kris007 · Post by **kris007** » Thu Oct 12, 2006 10:34 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.

ukyrvd · Post by **ukyrvd** » Thu Oct 12, 2006 10:38 am

punar_deep wrote:Yes they are part of the primary key and i have handled the same properly in my query itself.

it could be that you need to modify your query a bit ..

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!!

punar_deep · Post by **punar_deep** » Thu Oct 12, 2006 10:38 am

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.

samsuf2002 · Post by **samsuf2002** » Thu Oct 12, 2006 10:42 am

the error u r getting is uasually bcz the row ur inserting is already existing check the logic u r using to update the key value if it exist,somehow u r inserting the same key

meena · Post by **meena** » Thu Oct 12, 2006 10:49 am

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.

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.

punar_deep · Post by **punar_deep** » Thu Oct 12, 2006 11:01 am

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.

ukyrvd · Post by **ukyrvd** » Thu Oct 12, 2006 11:13 am

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.

Good!!
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)
)