Member Avatar for inazrabuu

Dear Masters,

I have a problem on comparing data.
I have two databases that have a similar (if not same) tables, but holds different data, and I want to merge the two tables. But can't seem to figure the easy way to do that, again and again I use script (PHP) to do that, which is take a long time.

The tables which I mentioned is like this, I have two databases, A and B, stored in different server, in each of the databases, I have table called t, so it will be like A.t and B.t. The t table holds schema like

id, username, email, password.

The thing is, each of the table, holds different data, yet many of them share the same data in terms of username or email. One of the table has more records than others, say, A.t has more records than B.t. Now, I would like to merge them into one table, with A.t to be the main reference (because it holds more records). So, any of record in B.t that has similarity with record in A.t will have to be thrown out, and any record that is different, will have to be putted in A.t.

Are there any ways to do that within database level?

Any guidance is truly appreciated, please enlightened me :D

Thanks in advance.

  • 2 Contributors
  • 1 Reply
  • 87 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by darkagn
Member Avatar for darkagn

Hi inazrabuu and welcome to DaniWeb :)

You can nest SELECT statements in an INSERT statement to get you what you need. For example,

-- insert into which table the values
INSERT INTO A.t (id, username, email, password) VALUES
-- from the other database
(SELECT id, username, email, password FROM B.t 
-- if they are not already in the other database
WHERE id NOT IN
(SELECT id FROM A.t))

This example assumes that the ID's match for the records in A.t and B.t, but you can expand it to suit your needs. Test this statement first be leaving out the first line of the above code and just selecting the records that will be inserted (ie just the nested SELECT statements).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.