Best way to import 50,000+ records?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

Our office is currently running a dinosaur of a character based ERP. We
are, however, able to dump our data into Excel. What we’re looking to do, on
a weekly basis, is import the ERP’s Products table into Access from Excel
(50,000+ records). Should we import our data into a new table every week,
and then run a duplicates query to eliminate the duplicates before appending
to the main table, or is there a better way, considering we probably add less
than 50 records per week?

Thanks for any advice.
 
Gina

Depends on how you define "duplicates". If there's a unique primary key
(recordID) in the "incoming" data, and you've captured that in your
permanent Access table, any attempt to append a "duplicate" row will fail
("key violation error") ... and that's OK -- that's what you want.

If you are saying all the fields have to match, what are all the fields?
Are you confident that your Access data structure is well-normalized (hint:
Excel spreadsheet data rarely is, but your ERP might be)? One approach if
you have multiple fields on which you are checking for duplication would be
to create a (unique) composite index on all those fields (there are limits,
and it does slow performance). Again, as in the case with a unique primary
key, a unique index will prevent "duplicates" from being added.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
In addition to what Jeff had to say, I think you may want to consider linking
to the Excel sheet rather than importing it. Then create an Append query
with filtering set up to append only the new records to your production
table(s).
You would save the import time and it may not bloat as much as importing a
large amount of data on a regular basis.
 
Back
Top