KGJ-Dev (8/30/2015)
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.
I believe you're missing the point. Just because something is in a separate file is no guarantee that it won't also be in another file. You could still end up with duplicates. The transaction number would have to be included from the source, not from anything you do after you receive the file.
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.
I don't believe that anything in this needs to be included in explicit transactions. If you import to a staging table and process it there, you can make the implicit transactions run very fast and there will be no need for multi-query explicit transactions because you're not yet affecting permanent data. The only thing that I saw in your original code was to insert unprocessed rows from the dump table to a process table. If you do the staging table thing, then even that's not "permanent" and you can skip having any explicit transactions.
Even the upcoming archive process is like that. You insert to the archive and don't delete until the insert is successful.
--Jeff Moden
Change is inevitable... Change for the better is not.