Merge statement for merging data of tables from two different databases

[Raj Kumar Bindal]

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]

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]

I am using oracle 10g

[Martin Vashko]

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]

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]

Hi all,

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

[Martin Vashko]

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]

Ok thanks. I am planning to implement Merge functionality myself.

[Rob Spoor]

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

Please EaseUp. There is no such thing as urgent around here.