Hi
I have three tables t1,t2,t3 and t4 where t3 is master table and t4 is detail table. I am inserting in t3 data selected from t1; inserting in t4 data selected from t2. Now, after comparing the totals of table t3 and t4; doing rollback or commit. Is this approach fine?
Or
Select data from the tables first and perform comparison, then insert data. But this approach requires firing multiple select queries twice in case of correct comparison.

Please suggest.

Does the table count have to match, or is it that the number of inserts have to match? If the latter, you can get that right away with a %XXX cursor variable. You can look for what XXX translates to (hint: some are named found, notfound, open, the one you want has "count" in it).

Hi
Comparison is OK. That can be done. My question is that which approach amongst the following is better:-
1. First firing insert statements,
then comparing the data of table
and in the case the required data is not inserted, performing rollback

or

2. First comparing data without insertion.
then firing insert statements
performing commit
But using the second approach, cursors have to be created twice for the same.

Okay, thanks for ignoring the hint.

Every SQL statement involves a cursor.

This is one of those examples where someone refuses to trust math. You have ten pencils. Counted them and saw for yourself that this is true. I take away two and tell you that. How many pencils are left? You turn around and physically count them again instead of believing that 10-2=8. Have fun with that approach.

"Is this approach fine? "

No, as Steve pointed out, it's anything but fine.

My principles are:
- keep my transactions as short as possible
- if I have some sort of verification that can result in a rollback, I will make them before the start of the transaction

So, I think it's ok to follow this approach:
- make a SUM() or COUNT() (I don't know what you need) on t1 and t2
- compare them
- if it is ok, start the transaction

Sum or count - we don't know what you need either.

"totals of table t3 and t4" is vague.

"I have three tables t1,t2,t3 and t4 where t3 is master table and t4 is detail table. I am inserting in t3 data selected from t1; inserting in t4 data selected from t2. Now, after comparing the totals of table t3 and t4; doing rollback or commit. Is this approach fine? "

Why all of this moving of rows to different tables?

Only the DBA should move rows around between tables, it;s very rare within applications.

Why not just use attributes, and keep it all in one table?

Actually, they are two different databases. So, need them to be moved. And this would be a better statement that I am performing some reconcilation on the data in destination and source schemas. My question is that I am creating same cursors again if I follow the approach of reconcilation first. And then putting in the database. What do you say?