Printable View
Hello ...
I need some help ...
I need to update the records in the master table from the temp table if the master record is present & insert the new record if record doesn't exist in the master table.
I should perform this as one time batch process ..
any idea ?
thanks
9i? Look at the merge command.
No ..I'm on Oracle 8.0
You'll be wanting to use PL/SQL, cursors, and loops. Cursor for each table. Loop thru each cursor comparing values/existence/whatever it is that makes you decide to insert/update from the temp table. All of this done in a PL/SQL prodedure. Piece of cake.
Every row in the temp table should be checked against 1 million records ?
if the TEMP table has 1 million records ..do you think PL/SQL Cursors would be the efficient way ?
You definitely don't want to do cursors.
You want to do:
followed byCode:UPDATE
MainTable MT
SET
(
Col2 ,
Col3 ,
Col4
) = (
SELECT
Col2 ,
Col3 ,
Col4
FROM
TempTable TT
WHERE
TT.Col1 = MT.Col1
)
WHERE
EXISTS
(
SELECT
1
FROM
TempTable TT
WHERE
TT.Col1 = MT.Col1
)
- ChrisCode:INSERT INTO
MainTable
(
Col1 ,
Col2 ,
Col3 ,
Col4
)
SELECT
Col1 ,
Col2 ,
Col3 ,
Col4
FROM
TempTable TT
WHERE
NOT EXISTS
(
SELECT
1
FROM
MainTable MT
WHERE
MT.Col1 = TT.Col1
)
Three issues.
1) You could provide more information about the size and scope of things you want to have done when you first post your question. Are you asking about a million records because you don't believe it can be done, or because you think I am stupid for suggesting the PL/SQL approach? That's just one way to do things.
2) Setting the methodology aside for the moment, based on your problem statement (checking records in a temp table against a master table, then inserting or updating depending on the case), does it not strike you as being obvious that you will be doing a record-by-record comparison in some form or another? The insert case is simple. Not there, insert it. What is your update criteria? How else will you know what to do with a record in the temp table?
3) Is PL/SQL the best way for your application? Who knows? Maybe you should consider COBOL or some other procedural call to do the work. You can look in just about any programming logic book and see how this problem is done - on a daily basis by a gazillion companies dealing with a bazillion records. Banking/updating customer accounts is a typical example of comparing a record in a temp table to a master table.
I see the crux of that rant as:
My reply would be yeah, but *how* that comparison is done is of great consequence. SQL is almost always the best way to go. If it can't be done in SQL, then PL/SQL. If it can't be done in PL/SQL, then Java or something else *on the server*. Finally, the worst way would be in some other language across the network.Quote:
Originally posted by stecal
Three issues.
2) Setting the methodology aside for the moment, based on your problem statement (checking records in a temp table against a master table, then inserting or updating depending on the case), does it not strike you as being obvious that you will be doing a record-by-record comparison in some form or another?
The rest of the rant (not that rants are bad - I do them all the time :)) falls in behind the same argument. Yes, you could open any programming book and see how to do it inefficiently in any language. The SQL solution I provided is most likely faster than any other language.
But basically, I'm not sure what set off this rant. The original question was valid, the response was reasonable, and the question about efficiency seemed genuine and was, as it turns out, dead on. Looping through a million records and testing each one seperately inside a PL/SQL cursor would indeed be slow. Nobody thinks you're stupid, or at least I don't think anybody insinutated that:D.
- Chris
I didn't mean for it to sound like a rant. I feel like I walked into one of those seemingly easy questions to answer and was met with one of those "oh, by the way, this has to work for a million records, you must be kidding about your answer" replies. For his million records, as it turns out, there could be anywhere from one million to two million SQL queries (you test for inserts first and the temp records are all new = one million, but if they are all updates, you're doing two queries, so there are some design issues to consider). Using a PK, the inserts are easy. The updates could be more complex because of what the update condition is, i.e., what's being tested, but why bother testing? If the PK is in the temp table, then do a blanket update of that record in the master table. Either way, SQL or other language, there are a lot queries. Do you want to do them one at a time or group them (cursor)? I agree that for a large number of records that cursors are probably not the best approach
You can't be serious when you suggest using "Cursor for each table. Loop thru each cursor comparing...". Why would you need more than one cursor? What use would it be of a cursor on a *master table*? And you don't need to perform any comparison, Oracle will do it for you (unless original poster will come back explaining his master table has no primary key;)).Quote:
Originally posted by stecal
You'll be wanting to use PL/SQL, cursors, and loops. Cursor for each table. Loop thru each cursor comparing values/existence/whatever it is that makes you decide to insert/update from the temp table. All of this done in a PL/SQL prodedure. Piece of cake.
But as Chris said, pure SQL approach should be inspected first to see if it's feasible.Code:BEGIN
FOR r IN (SELECT * FROM temp_table) LOOP
BEGIN
INSERT INTO master_table (colA, colB, ....)
VALUES (r.col1, r.col2, ....);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
UPDATE master_table SET colB = r.col2, .....
WHERE colA = r.col1;
END;
END;
END;