Best way to import 50,000+ records?

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.
 
J

Jeff Boyce

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
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top