File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Merge statement for merging data of tables from two different databases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Merge statement for merging data of tables from two different databases" Watch "Merge statement for merging data of tables from two different databases" New topic
Author

Merge statement for merging data of tables from two different databases

Raj Kumar Bindal
Ranch Hand

Joined: Apr 15, 2006
Posts: 418
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.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19718
    
  20

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.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Raj Kumar Bindal
Ranch Hand

Joined: Apr 15, 2006
Posts: 418
I am using oracle 10g
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Apr 15, 2006
Posts: 418
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

Joined: Apr 15, 2006
Posts: 418
Hi all,

I need help on this urgently. Please tell me how to use merge with two databases.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Apr 15, 2006
Posts: 418
Ok thanks. I am planning to implement Merge functionality myself.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19718
    
  20

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

Please EaseUp. There is no such thing as urgent around here.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Merge statement for merging data of tables from two different databases