2

I have two databases; Database A and Database B. Database A is in my computer and Database B is hosted in a online server. These both databases contains same tables with same data types but Database A will contains more data than the Database B. I want to select all the unique data from Database A.

Database A Table A

Id  | FName      | LName
-----------------------------
1   | Jhone      | A
2   | Alice      | B
3   | Bob        | C

Database B Table A

Id  | FName      | LName
-----------------------------
1   | Jhone      | A

I want to select:

2   | Alice      | B
3   | Bob        | C

How can I do this?

chalitha geekiyanage
  • 6,464
  • 5
  • 25
  • 32
  • You can do this simply by sending all the data from local DB to the remote DB and let it decide. However that is extremely unefficient, what are your restrictions? Is this operation going to take place often? – amit Dec 24 '14 at 13:49
  • you can link two servers if yours server type allows it. For example: http://msdn.microsoft.com/en-us/library/ff772782.aspx Then by one query you can link both tables. – pasha701 Dec 25 '14 at 10:37

2 Answers2

0

Option 1

OK so the boring (and hard) way to do this is to issue separate SQL queries to separate databases, and then write a pile of java to compare the results. That can totally be done, but ouch.

If you need to do this sort of thing on a regular basis, you should use a federated database or virtual database tool. Basically what those tools do is create a virtual "uber database" that contains both of the ones you need to query inside of it. So you can connect to the virtual database and issue SQL queries that go against both DBs.

A simple example is UnityJDBC. If you search around, there are others as well, like VJDBC.

You're then just going to write a single SQL query against both DBs that gives you what you want, which is going to be a left join from Database A Table A to Database B table A.

In UnityJDBC, it might be something like this:

SELECT ID, FName, Lname
FROM Database_A.TableA, Database_B.TableB
LEFT JOIN Database_B.TableB ON Database_A.TableA.ID = Database_B.TableB.ID;

This query isn't that complicated, but it would only be possible because you used a virtual DB layer.

Option 2

Use an Extraction, Translation, and Load tool (like Pentaho Kettle) to pull data from one to another. You can use a GUI application to do this, or you can use their open source java libraries and build it into your application.

Community
  • 1
  • 1
FrobberOfBits
  • 17,634
  • 4
  • 52
  • 86
  • 1
    I have done this before using MS Access with Linked tables which is an approach highlighted in the given link as alternative to using their product. So if you are running on Windows and have MS Access..... – Alan Hay Dec 24 '14 at 13:59
  • Actually what I want to do is to update the Database B with the data in the Database A at the end of the day when user clicked a button. Is their a simple way of doing this other than this? – chalitha geekiyanage Dec 24 '14 at 14:04
  • See updated answer. I don't know what you mean by "simple way". You could just write a bunch of java code like I said in the first paragraph of option 1, but I don't think that will be simpler in the long run. I'm trying to suggest something that reuses existing good software rather than advising you to write more of your own. It takes time to learn the other software, but it's much less painful and less likely to be buggy. – FrobberOfBits Dec 24 '14 at 14:16
  • what do you think about federated tables, if idea is keeping database A and database B's datas same? but it may be slow.. – İlker Korkut Dec 24 '14 at 14:42
  • Federated tables can be slow, but that's because you're hauling data across the network and comparing it to something else. It's worth pointing out that OP **must** do that in order to solve this problem. The only issue is whether OP will re-implement it on his/her own, or reuse code to do the same thing. Is it really better to issue two separate queries and then to do the join in java code on the client? – FrobberOfBits Dec 24 '14 at 14:44
0

If you only need to do this once, and the data isn't too large

  • export both tables to CSV's
  • put them on the same box

  • use grep to find the columns in A that are not in B

    grep -vf DB_B_Table_A.csv DB_A_Table_A.csv

jastr
  • 881
  • 1
  • 9
  • 19
  • 1
    OP is trying to wire this to a button in an app; this answer requires a lot of manual work, and assumes UNIX. Swallowing up this functionality in a java app would likely be messy. – FrobberOfBits Dec 24 '14 at 14:45
  • 1
    True, true, and yet OP flagged this java and database. There's a practically infinite number of ways you could do this, including with Ruby, or any other tech under the sun. The posing of the question and the tags pretty much presumes java and databases, not shell, or MS Access, or CSV, or Ruby, or (1,000 other options here). – FrobberOfBits Dec 24 '14 at 15:03