AndrewDB Posted November 9, 2015 Share Posted November 9, 2015 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 9, 2015 Share Posted November 9, 2015 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. 1 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now