tag:blogger.com,1999:blog-13668718.post3763921845874225384..comments2023-10-30T12:24:51.923+00:00Comments on Halis way: Oracle merge-statementHampushttp://www.blogger.com/profile/12826832242493809239noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-13668718.post-40273727841983273242011-07-08T10:25:31.279+01:002011-07-08T10:25:31.279+01:00Very helpful post. I have not used merge statement...Very helpful post. I have not used merge statement so far because I think its very slow but I do strongly agree with the solution that you have provided. I an sure it will be faster option to use on the process that I am working on. <br /><a href="http://www.panayainc.com/" rel="nofollow">oracle ebs r12</a>Andreahttps://www.blogger.com/profile/16695004812462620900noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-30204280912348342752010-04-07T20:50:47.666+01:002010-04-07T20:50:47.666+01:00Hi,
I have a need to merge data from 2 databases...Hi, <br /><br />I have a need to merge data from 2 databases (same schema). Can the merge statement be used to merge data from the same table name in different databases? I would in this case be adding data from Oradb2 into the existing data in Oradb1 for example where both have the same schema, but different records, keys are managed so no collisions etc.Unknownhttps://www.blogger.com/profile/13958837377106630756noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-68812232500028553692009-06-18T20:20:05.969+01:002009-06-18T20:20:05.969+01:00Very good example. Thanks!Very good example. Thanks!rpunginhttps://www.blogger.com/profile/17824019606931202726noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-15662864410296740072009-04-17T20:30:00.000+01:002009-04-17T20:30:00.000+01:00Hi that was a nice piece of advice...is there anyt...Hi that was a nice piece of advice...is there anything we have to merge records in the same table?Rajesh Shahhttps://www.blogger.com/profile/10800803801115961710noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-70510203375606044852008-09-03T18:42:00.000+01:002008-09-03T18:42:00.000+01:00Thanks for this example. I had never noticed the ...Thanks for this example. I had never noticed the USING clause in any other examples, so never could get it to work.<BR/><BR/>Thought that may say more about my reading skills than your example??? :-/Stewhttps://www.blogger.com/profile/05177922393895344852noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-7011339995541099552008-08-25T16:08:00.000+01:002008-08-25T16:08:00.000+01:00Pramod, did you find a solution yet? -PDPramod, did you find a solution yet? -PDPDhttps://www.blogger.com/profile/13223873696218872034noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-68044150772231829472008-05-22T11:59:00.000+01:002008-05-22T11:59:00.000+01:00how to merge records of datatable and physical dat...how to merge records of datatable and physical database table from c#.net.<BR/>One idea is first create a temp table and insert records of datatable, then apply merge statement on temp table and physical database table. <BR/>but i don't want to create temp table.<BR/>I have a very large table consist millions records.<BR/><BR/><BR/>Any idea...<BR/>Thanks in advance..<BR/>~pramodAGhttps://www.blogger.com/profile/07460165216927994929noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-37787649204075199012008-05-06T01:45:00.000+01:002008-05-06T01:45:00.000+01:00Nice article, especially the one line summary of t...Nice article, especially the one line summary of the MERGE statement<BR/><BR/>"if exists then update else insert"Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-13668718.post-92087374034755558892008-01-08T18:00:00.000+00:002008-01-08T18:00:00.000+00:00eian, yes, you can use both the "WHEN MATCHED..."...eian, yes, you can use both the "WHEN MATCHED..." and the "WHEN NOT MATCHED..." statements or either one of them by themselves.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-13668718.post-52731735995959420182007-12-08T06:44:00.000+00:002007-12-08T06:44:00.000+00:00Merge works great if you're doing an insert/update...Merge works great if you're doing an insert/update, but what if I have A0abc and I need to result in A1def and A2ghi where my key is A#? In other words, how can I tell it to delete the key that didn't match, without it affecting B#?Alanhttps://www.blogger.com/profile/02850260357908532001noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-62294085579185450962007-11-10T17:31:00.000+00:002007-11-10T17:31:00.000+00:00can you do an update if it doesnt match then ignor...can you do an update if it doesnt match then ignore if it matches?<BR/><BR/>cheersAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-13668718.post-57725545870592424602007-10-24T05:06:00.000+01:002007-10-24T05:06:00.000+01:00Very insightful and practical advice.A couple obse...Very insightful and practical advice.<BR/><BR/>A couple observations for Oracle loading from experience in a VLDB installation.<BR/><BR/>You can use Oracle External Tables combined with MERGE and/or INSERT to easily load data from a flat file external source. Breaking up the data into multiple data files referenced by the external table will allow Oracle to read them in parallel.<BR/><BR/>I've found that MERGE can be slow if your table has a large number of indexes. Periodic reordering of the PK and ordering of the data to load by PK will help with large volumes.<BR/><BR/>In some cases with large tables and you're changing more than 20% of the records it can actually be faster to create a new table and then flip the table-names to put the new table into production.Jeff Parkerhttps://www.blogger.com/profile/13944901855924222607noreply@blogger.comtag:blogger.com,1999:blog-13668718.post-19655243370981895182007-08-28T22:18:00.000+01:002007-08-28T22:18:00.000+01:00How does this work with large volumes of data? Or ...How does this work with large volumes of data? Or is the following better for large amounts of data:<BR/><BR/><I><BR/>truncate some_table;<BR/>insert into some_table as<BR/>select *<BR/>from other_table;<BR/></I>Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-13668718.post-64562852851556585262007-05-02T16:35:00.000+01:002007-05-02T16:35:00.000+01:00Thanks--very helpful! I was toying with the idea o...Thanks--very helpful! I was toying with the idea of doing a pure delete/re-insert, but I think a MERGE may be faster for the amount of data I'm handling, after reading your entry. Thanks again.Laurahttps://www.blogger.com/profile/05037989321662439225noreply@blogger.com