insert question

[wh0wants2know]

I have two tables, dbo_postalcode and ACI. some of the records in ACI may or may not be in dbo_postalcode, so what i want to do is insert only the values in ACI that are not present in dbo_postalcode into dbo_postalcode. my sql query is thus:
<code>
INSERT INTO dbo_PostalCodes ( PostalCode, City, State, CountryID, AirportID, ACIArea )
SELECT ACI.zip, ACI.city, ACI.state, ACI.CountryID, ACI.ID, ACI.zone
FROM ACI where not Exists (select * from dbo_PostalCodes where (dbo_PostalCodes.city = aci.city AND dbo_PostalCodes.state = aci.state And dbo_PostalCodes.postalcode = aci.zip and dbo_PostalCodes.airportID = aci.ID ) )
</code>

this thing runs for about five minutes and then crashes and nothing happens. dbo_postalcodes is a linked table from my sql server and ACI is an access table. ACI is about 45000 records and dbo_postalcodes is about 15000 records, there's probably an overlap of about 8000 records. is the even going to work this way, or do i need to do something different to make this happen? why is it crashing?

 

[LNBruno]

Disclaimer: I've had the flu for the last week, so...

Looks like you're on the right track. Would suggest you change "select * from dbo_PostalCodes" to "select <just one field> from dbo_PostalCodes"...

No reason to select all the fields when one will do and that should speed up the processing.

< M!ke >

 

[PHV]

You may try this:
INSERT INTO dbo_PostalCodes (PostalCode, City, State, CountryID, AirportID, ACIArea)
SELECT A.zip, A.city, A.state, A.CountryID, A.ID, A.zone
FROM ACI AS A LEFT JOIN dbo_PostalCodes AS P ON A.city = P.city AND A.State = P.state AND A.zip = P.PostalCode AND A.ID = P.AirportID
WHERE P.PostalCode Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244