updating/adding new records to existing tables (1 Viewer)

martinr

Registered User.
Local time
Tomorrow, 05:01
Joined
Nov 16, 2011
Messages
73
I have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.

Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?

If I run an update query I will have to manually add each field to the query.

the update query will not add 'new' records.
If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).

If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.

is there a better way to do this?
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:01
Joined
Jan 20, 2009
Messages
12,849
Join the old and new tables on the key field/s in an update query.

Change the join to an outer join with the arrow going from the new data table to the old data table.

It will update existing records and append the new ones.
 

Users who are viewing this thread

Top Bottom