• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Merge statement for merging data of tables from two different databases

 
Ranch Hand
Posts: 418
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I need to get data of a table from database db1 based on some condition and then insert/update it in table of database db2. Can anyone tell me if we can merge data from 2 tables in different databases using single merge statement. If yes, how we can do that?

If i get table data of one database in a resultset, how can i use that resultset in Merge statement to insert/update table in another database.

Thanks in advance.
 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It would help if you told us which database system you're using. MS SQL Server allows you to create queries with tables from multiple databases of the same instance. Using linked servers you can even create queries with tables from databases from multiple instances. Other database systems may not be as flexible.
 
Raj Kumar Bindal
Ranch Hand
Posts: 418
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using oracle 10g
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
First and foremost, make sure that you're not actually trying to implement something which is already provided by Oracle's tools. Oracle has a plethora of tools for different scenarios, including replication and streaming, and you might save yourself a lot of time and pain by using the correct tool for your task.

Anyway, in Oracle you can create a database link leading to another Oracle database, which does not need to reside on the same server. I'd suggest to read the documentation thoroughly, though it is easy in principle, it requires some configuration on the database server and contains a few limitations.

You'll want to read everything on the CREATE DATABASE LINK command and probably all references you'll find on that page.

Once you setup the link, you can use mix local and remote tables in the same SQL statement (the documentation will tell you how to go about it). As far as I know, some operations can be sped up by having them processed by the remote database, but I didn't ever need to go into such details.
 
Raj Kumar Bindal
Ranch Hand
Posts: 418
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Martin, thanks for your response. I can't use any tools or oracle DB links because of some restrictions. I have to go through JDBC only. I will appreciate if you give a solution for the mentioned issue using JDBC Merge statement only.
 
Raj Kumar Bindal
Ranch Hand
Posts: 418
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

I need help on this urgently. Please tell me how to use merge with two databases.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Raj Kumar Bindal wrote:Hi Martin, thanks for your response. I can't use any tools or oracle DB links because of some restrictions. I have to go through JDBC only. I will appreciate if you give a solution for the mentioned issue using JDBC Merge statement only.


In this case you simply cannot use the merge, you'll have to implement the merge logic in Java. There is no magic here, if you don't let the target database to "see" (link to) the source one, it cannot use the data from the source database.

You might also copy all data from the source database into the target one using JDBC (into a temporary table perhaps) and then use the merge statement; this would probably be easier, less error prone and in some circumstances might perform better than performing the merge logic in Java.

You might try to explain to whoever came up with "some restrictions" that these restrictions lead to more complex, more error-prone and less efficient solutions.
 
Raj Kumar Bindal
Ranch Hand
Posts: 418
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ok thanks. I am planning to implement Merge functionality myself.
 
Rob Spoor
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Raj Kumar Bindal wrote:I need help on this urgently.


Please EaseUp. There is no such thing as urgent around here.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic