Importing Unique Records Into MySQL using a Stored Procedure

[AndrewDB]

Hi All.

 

I'm a MySQL Newbie and would really like some guidence.

 

My employer has over 47K employees. I use a MS Access application to import employee information from MS Excel into an Access data table on a monthly basis. However I need to exclude records of employees that have previously been imported. Currently I import the records into a table and then run the query shown below.

 

INSERT INTO tbl_EmployeeDetails ( EmployeeNo, EmployeeFirstNames, EmployeeSurname, EmployeeID, DateOfBirth, ResidentialAddress1, ResidentialAddress2, ResidentialAddress3, EmployeeNo )SELECT tbl_ImportEmployee.EmployeeNo, tbl_ImportEmployee.EmployeeFirstNames, tbl_ImportEmployee.EmployeeSurname, tbl_ImportEmployee.EmployeeID, tbl_ImportEmployee.DateOfBirth, tbl_ImportEmployee.ResidentialAddress1, tbl_ImportEmployee.ResidentialAddress2, tbl_ImportEmployee.ResidentialAddress3, tbl_EmployeeDetails.EmployeeNoFROM tbl_ImportEmployee LEFT JOIN tbl_EmployeeDetails ON tbl_ImportEmployee.[EmployeeNo] = tbl_EmployeeDetails.[EmployeeNo]WHERE (((tbl_EmployeeDetails.EmployeeNo) Is Null));

This has worked very well except that query takes more than 3 hours to run. I have now been instructed to migrate the Access database to MySQL located on a LAN network server. The speed has dropped even further to approx 12 hours.

 

tbl_EmployeeDetails.EmployeeNo is the primary key field.

 

I have tried rewriting the query script in MySQL but just cannot get it right.

 

All assistance or suggestions will be appreciated.

 

Best Regards

Andrew

 

 

 

 

 

 

 

 

 

 

[justsomeguy]

If you have a primary key then you can use INSERT IGNORE to insert everything and ignore records where the primary key already exists. That's a lot faster than running a query to check if every record exists.