Hi,
I am trying to do an insert query based on a table value, and a constant. I tried using a join to eliminate rows i don't need. It works in code (when it doesn't corrupt) but won't compile as a query because it doesn't recognize the alias'd table.
I have an employee table and a compliance table. I need to add employees to the table based on certain criteria.
The original query is:
INSERT INTO tblEmployeeCompliance(EmplID, ComplianceDescr)
SELECT A.EmplID, A.ComplianceDescr
FROM (SELECT EmplID, HireDate 'Information Security' as ComplianceDescr FROM tblEmployee) as A
LEFT JOIN tblEmployeeCompliance ON A.EmplID = tblEmployeeCompliance.EmplID, A.ComplianceDescr = tblEmployeeCompliance.ComplianceDescr
WHERE tblEmployeeCompliance.EmplID is null and tblEmployeeCompliance.ComplianceDescr is null
AND A.HireDate > #1/1/2011#
This runs fine in my dev version, then I import it to a new thing to send to test and it corrupts the database 100% of the time.
So I tried pasting the SQL into a query and doing a db.execute("testquery") and it doesn't recognize the Alias'd table in the compiled query.
Then I tried doing a subquery appraoch:
INSERT INTO tblEmployeeCompliance(EmplID, ComplianceDescr)
SELECT EmplID, 'Information Security' as ComplianceDescr
FROM tblEmployee
WHERE EmplID NOT IN(
Select EmplID
FROM tblEmployeeCompliance WHERE ComplianceDescr = 'Information Security')
AND HireDate > #1/1/2011#
I was running this on my local drive and it took close to 5 minutes to run, using about 1/4 of the data that's in production and test. It would be unbearably slow in test/prod.
I have to run 13 versions of this query because of different "Where" criteria for each compliance type so even running 5 minutes at a time 13 time is still over an hour. How can I get this thing to work without corrupting and being super supremely slow?
Thanks
-Lee
Tidak ada komentar:
Posting Komentar