MS Access replace entire table contents Info: I am setting up a new MS Access database for a small team (~10 person) project. There is a table "A" which is never modified but has relations to another table based on the field "Tag Number". This other table "B" contains all the dynamic information which is modified regularly by the team. Once a week, we receive a new Excel spreadsheet that contains the latest engineering export, which contains the entire contents of the table A. Some new records will be added and some records will be gone every week.

My question is this:

How do I replace the entire contents of the A table? I want to ensure that the relation to B table stays intact. I could delete the entire table and copy/paste in from excel (70000 rows), or I can import the excel spreadsheet to a new table and then use a combination of Update/Append/Delete queries. (Update to modify existing records, Append to add the new non existent records and Delete to remove records no longer present in the latest spreadsheet.)

Or some other, better, method.

Help please?
As Access does not support UPSERT semantics you're stuck with something along the lines:


  insert ... into tbl
  select ... from tmp_tbl
  where not exists (select 1 from tbl where tbl.id = tmp_tbl.id)`


And similar for updates and deletes using outer joins where appropriate.

That means three SQL-statements.

See this question on SO.

Edit:

I would recommend not to delete records, but instead to mark them 'invalid', that way you can decide, what to do with tags in your other table, if necessary.