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.

Last Of The Summer Wine Britbox GIFfrom Last Of The Summer Wine GIFs

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.

EDIT

So, this happened again so I thought I'd better take some notes and use some proper SQL:

INSERT INTO
  `TARGET_TABLE`.campaign (
    product_prod_id,
    camp_date_begin,
    camp_date_end,
    date_created,
    camp_store_mode,
    camp_title,
    camp_comment
  )
SELECT
  product_prod_id,
  camp_date_begin,
  camp_date_end,
  date_created,
  camp_store_mode,
  camp_title,
  camp_comment
FROM
  `SOURCE_TABLE`.campaign a
WHERE
  a.camp_date_begin
    NOT IN (
    SELECT
      camp_date_begin
    FROM
      `TARGET_TABLE`.campaign b
    );

INSERT INTO
  `TARGET_TABLE`.campaign_details (
    product_prod_id,
    cpd_date,
    campaign_camp_id,
    cpd_status,
    cpd_attempt,
    last_updated,
    cpd_max_time
  )
SELECT
  product_prod_id,
  cpd_date,
  51,
  cpd_status,
  cpd_attempt,
  last_updated,
  cpd_max_time
FROM
`SOURCE_TABLE`.campaign_details a
  WHERE
  a.cpd_date
  NOT IN (
    SELECT
      cpd_date
    FROM
      `TARGET_TABLE`.campaign_details b
    );

INSERT INTO
  `TARGET_TABLE`.measure (
    meas_date,
    meas_speed,
    meas_direction
  )
SELECT
  meas_date,
  meas_speed,
  meas_direction
FROM
  `SOURCE_TABLE`.measure a
WHERE
  a.meas_date
  NOT IN (
    SELECT
      meas_date
    FROM
      `TARGET_TABLE`.measure b
    );

Please note the 51 is hardcoded and the result of the previous query inserting another row in the campaign table.