Question on temp table or Procedure

So once again in this system that I am converting over they have done something that I see as stupid but I would like to ask some experts. In this one area of the system it is transferring all banking to accounting, the two different systems.

This is the current process between program to database. They insert a bunch of data to temp table1, we will call tt1. Now on tt1 there is a trigger, that on insert of new data checks tt2 to see if the 4 ids with today's date already exist, if so it updates that record from inserted data and adds amount to what's already there. If it doesn't exist then it inserts to tt2 from it's inserted data. Simple trigger.

At the end of the program's sequence it insert's all data in tt2 with the 4 ids and today's date to final table, call it f1. Once done inserting all records with todays date to f1 from tt2, it erases everything in tt1 and tt2.

Now to me this seems like a lot I mean a lot of unnecessary work.

Am I right?

If so what would be better and easier.

Option1:

Run in my PHP script a select query on f1 to see if 4 ids exist with todays date. If so update and add to amount the current value. If not exist insert.

Option2:

Create a procedure to do all of that on f1 then call procedure.

Either way I remove both tt1 and tt2 and cut down time plus remove the stupid trigger and let my DB do less work.

Am I right in my assumption or am I being too naive?

Thanks guys. :thumbsup:

If there are really only a max of 4 records involved, I don't think it's worth the hassle of rewrite.

But otherwise: Of course you are right.

I'd be tempted to go the SP route, but that's my preference.

Well it's actually more than 4 records, it's 4 ID columns that get matched. But it's two temp tables and the final table per each part of the system. There are two parts, and the old system writes to these tables at least 6 times in each part. Since they are part of a while loop from another select statement, it inserts into the temps. Then at the very end it select all from the tt2 and inserts into final of both final tables.

So that's what I did, I cut the time in half and removed all four temp tables and just insert into the final tables from the get go. I didn't do a procedure since I'm under the gun and I'm still not so good with procedures even though they have been the primary talk we have had ha ha ha

So I didn't want to mess with it, but yeah it's a lot just to those temp tables. And looking back I could have done it better also, I could do an insert on exist update, but I don't want to go back right now ha ha.

I will fix it later.

Thanks ped!