How to merge two SQLite DB

[Wolfram]

Hello everybody.

I have two identical SQLite databases with different data. Some of data Records are in both databases and some are in one of them only.
What is the easiest way to merge them together in one database without having double?

[mk-soft]

Inconsistent databases are always a problem. If the data sets are not too large, create a common ":memory:" SQLite database from a select of the two databases.

Edit:
alternate show "ATTACH DATABASE"

[spikey]

Make backups before you start and plan carefully - otherwise you'll create a bigger mess!

You'll need answers to the following questions:
At what level will you have trouble? And at what point in time did the replica's diverge? Short term or long term? (In terms of IPK's under x are ok, IPK's over x are not ok).
Are we talking merge conflict type trouble (data will have been modified in both tables but you still have IPK integrity)? or loss of IPK integrity (IPKs have been used divergently in both replicas)? They need handling differently.

As mk-soft says you will need to have attached one to the other (in this case I'm assuming one is attached as replica1 and the other replica2)...

These will find missing IPKs which will point to missing rows:

Code: Select all

select * from replica1.<table> where <IPKname> not in (select <IPKname> from replica2.<table>);
select * from replica2.<table> where <IPKname> not in (select <IPKname> from replica1.<table>);

Something like this will look for rows where a field named 'value' doesn't coincide across replicas:

Code: Select all

select replica1.<table>.<IPKname>, replica1.<table>.value, replica2.<table>.<IPKname>, replica2.<table>.value 
from replica1.<table> join replica2.<table> on replica1.<table>.<IPKname> = replica2.<table>.<IPKname>
where replica1.<table>.value <> replica2.<table>.value;

You'll need to use field(s) whose contents will properly indicate IPK divergence has occurred.

How you fix the mess depends on the number of messed up table relationships but you will need to be able to call a halt to the divergence in both replicas at some point in time to fix it.