Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have one table in oracle where data gets inserted from some third party. I want to populate master tables from that table. So, what will be the best way performance wise using collection.

E.g. Suppose, the table into which data will get populated from third party is 'EMP_TMP'.

Now I want to populate 'EMPLOYEE' master table through procedure which will get populated from EMP_TMP Table.

Here again there is one condition like IF SAME EMPID (this is not primary key) EXISTS then we have to UPDATE FULL TABLE which consists of SAME EMPID ELSE we have INSERT NEW RECORD.

[Note: Here EMPID is VARCHAR2 and EMPNO will be primary key where we will use SEQUENCE]

I think here merge will not perform much better performancewise since we cant use collection in MERGE statement.

share|improve this question

2 Answers 2

Create a procedure, you need to be using PL/SQL. Do an update first then test sql%rowcount. If it is 0, no updates where done and you have to do an insert instead.

I think that this is fairly efficient.

pseudo code

Update table;
if sql%rowcount = 0 then
   //get new sequence number
   insert into table;
END IF;
COMMIT;

HTH
Harv

share|improve this answer

Well, if performance is your primary consideration, and you don't like MERGE, then how about this (run as script, single transaction):

delete from EMPLOYEE where emp_id IN (
select emp_id from EMP_TMP);

insert into EMPLOYEE
select * from EMP_TMP;

commit;

Obviously not the "safest" approach (and as written assumes exact same table definitions and you have the rollback), but should be fast (you could also mess with IN vs EXISTS etc). And I couldn't quite understand your post if emp_id or emp_no was the common key in these 2 tables, but use whichever makes sense in your situation.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.5.22.2603\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </nos"], "copyright__copyright__copyright": [" assumes exact same table definitions and you have the rollback), but should be fast (you could also mess with IN vs EXISTS etc). And I couldn't quite understand your post if emp_id or emp_no was the common key in these 2 tables, but use whichever makes sense in your situation.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}