Hi Jeff,

Thanks you so much for your elaborated explanation and really helpful for me to move forward. I am in the position to explain few other points,

Regarding the point #1 - I have transactionNumber column in the testdump table and i am generating this number based on file data. Sorry that i didn't mention in the sample table structure. I thought something to tune the query and thats why didn't mentioned about this. I apologize for missing that column. So duplicated would be avoided and i am already taking care of them.

Regarding point#2 i totally agree because this is what i suggested to my customer. Dump the data from file into staging table and do the process then move the processed data to Master table.

Regarding Point#3 . So far i couldn't think it of because this process will take place another one month to implement in my production. So I don't have any idea now about archive procedure. As far as i heard, every three month they will do the archive action

Regarding point #4: I understood your point, right now more tables have the reference to the CustProduct table and i couldn't do modification on the IdCustProdcut.

I will watch the video you prescribed. Thanks a lot for your time on this. One last question that i was asking question about Transaction in my original post.

Question is below,

1. As you have noticed that all the logic's are inside transaction.Lets assume that if the process dealing with more data and if any exception occurs during the process, will the card and product table gets locked until rollback completes?

Because i don't want to lock those tables at any cost because it will be used by my website. Please suggest me best option to tackle this situation and if you have any sample please share it.

thanks