update SQL with data from another

[pmsbony]

I have a table with approx. 1.8 million records. I recieve data updates on it quarterly. I can create this update as a new table, but i need to insert any data from the new table that is different from the old.

I need to insert data if it meets the following criteria are met:

1. data in field name 1 does not exist in old data

as a seperate issue I also need to create a table/query which will tell me where the data in field name 1 is the same in both tables, but the data in field name 2 is different, without updating any data.

cheers

bony

 

[mikehilly]

Write an Insert query using a select query that uses a left join. The Join would find all records in the new table and only those that match in the old table you can then use a where clause on the primary key of the old table which looks for Null values. They will be Null in Old where they do not exist.
Set theory is a good way of visualising this sort of thing....
Here is an example..
Insert Into Old (<list of fields>) Select (<list of fields>) from New LEFT JOIN old ON old.oldID = new.oldID where old.oldID is null