How to update table from imported data 1

[cbase]

I have a Table A in the main database that I require to be updated with
information that is imported from the web and then placed in Table B in the
same database.
So far so good, I need to update Table A from Table B only when the primary
keys match. In addition I would like to monitor/record the changes.
Table B will be updated daily and may or may not contain the records in
Table A. If in Table B but not in Table A then Table A needs to have an
append action.
The end result would be a Table A that contains the most current and
complete data, Table B is cleared down prior too the next days import.

Any advice or ideas will be very welcome

 

[markgrizzle]

Do it in 2 steps.

to update table a, create an update query, include both tables, and join them on the primary key. Load the grid columns with required fields. Only matching records will be updated.

UPDATE tblA INNER JOIN tblB ON tblA.KeyField = tblB.KeyField
SET tblA.KeyFieldIsbn = tblB.KeyField;

To monitor/record the changes, you could use a loop to match and compare record values and store changes in a third table, or simply copy table a as table history, and write all the old table a values prior to each update.

To append new table b records, create an append query and load table b rows missing from table a.

INSERT INTO tblA ( Field1, ... )
SELECT DISTINCT tblB.Field1
FROM tblB LEFT JOIN tblA ON tblB.KeyField = tblA.KeyField
WHERE tblB.KeyField Is Not Null AND tblA.KeyField Is Null;