Thursday, 14 July 2022

Merging two SQLite DBs

We've got an MVAS (well, we've got two, but only one team to manage the one) in Witchford, and I'm part of the team that manages it.

I hold ladders and carry batteries, and it's not all that hard a task, quite good fun. One chap downloads the data and lets me cast my eye over it (it's stored as in SQLite database). He went on holiday a little while back, so I had to collect the data. That meant there was an issue with the data integrity between his copy of the DB and mine. Thus, I was left with a need to merge two SQLite DBs; with the help of SQLiteStudio, it was pretty easy to resolve. Each DB has three tables: campaign, campaign_details and measure.

Thinking about it, after going through this process, I realised I just needed to copy over the entirety of my DB to him; it had the same data apart from the latest readings... but hindsight is a beautiful thing.

Anyway, there was an extra row in campaign - so that was easy to copy using some SQL; there were an additional 13 rows in campaign_details - again, dead easy to copy over using a bit of SQL. But, there were an extra 17K+ rows in measure...

I got in touch with my mate Oliwia to see if she could help, but she was busy, so I had to cobble together my SQL; this is what I came up with:

INSERT INTO 
  original.measure
SELECT 
  *
FROM 
  new.measure a 
WHERE 
  a.meas_id 
NOT IN (
  SELECT 
    meas_id
  FROM 
    original.measure b
)

Not pretty, but it worked! I blame the heat for not realising sooner

No comments:

Post a Comment