Post reply

Importing Zip Codes

  • December 16, 2003 at 1:29 pm

    #161264

    I am trying to import new zip codes into an existing database. However, I only want to add new ones while preserving the existing ones. Besides adding completely new zip codes, I only want to add those where something in the combination of zip code, county, city, and state is different.

    I am having difficulty identifying those records. Does anyone have a suggestion?

    Thanks,

    charlesd

  • December 16, 2003 at 1:39 pm

    #486511

    What is the structure of your tables?

    You could import all of the zip codes into a temp table and then use a join to determin which zip codes needed to be inserted/updated on the production table.

  • December 16, 2003 at 1:44 pm

    #486515

    insert into old_zip_table select * from new_zip_code where zipcode not in (select zipcode from old_zip_table)

    quote:

    Besides adding completely new zip codes, I only want to add those where something in the combination of zip code, county, city, and state is different.

    Need some samples here.

    Edited by - allen_cui on 12/16/2003 1:44:47 PM

  • December 16, 2003 at 1:51 pm

    #486519

    Thanks. I am basically doing just that, importing all new zip code records into a temporary staging table. One table, ZIP_CODE_IMPORT contains the following fields: ID (UniqueID), ZipCode, Primary (Bit), City, State, CountyNumber, and CountyName. The existing zip code table, ZipCodes contains the following fields: ZIP, City, County, and State. The problem is that I don't know how to construct the join for inserting the new records where any one of the four fields, in combination, is different.

  • December 16, 2003 at 1:59 pm

    #486521

    Insert into Zip

    Select ZIP, City, County, State

    From ZIP_CODE_IMPORT

    Where ID Not in (

    Select ID

    From ZIP_CODE_IMPORT a,

    Zip b

    Where a.Zip = b.Zip

    And a.City = b.City

    And a.Country = b.Country

    And a.State = b.State

    )

  • December 17, 2003 at 6:16 am

    #486639

    I think a join would be faster

    INSERT INTO ZipCodes 

    (ZIP, City, County, State) 

    SELECT ZipCode City, CountyName, State) 

    FROM ZIP_CODE_IMPORT i 

    LEFT OUTER JOIN ZipCodes z 

    	ON z.ZIP = i.ZipCode 

    	AND z.City = i.City 

    	AND z.County = i.CountyName 

    	AND z.State = i.State 

    WHERE z.ZIP IS NULL

    Is there a possibility of duplicate ZipCode, City, State, CountyName on the input?

    Edited by - davidburrows on 12/17/2003 06:18:32 AM

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 17, 2003 at 7:14 am

    #486657

    Thanks to jxflagg and David for your replies. I tried jx's method and that seemed to do the trick. However, I'm sure that David's join method would also work. I'll give that a try for comparison, but performance doesn't seem to be an issue. As far as uniqueness is concerned, I believe that each combination of ZipCode, City, State, CountyName on the input record is unique.

    Thanks again!!

  • December 17, 2003 at 11:59 am

    #486767

    Simple adding new zipcodes and keeping old ones may not work correctly.

    For example: Lee Mont, VA zipcode used to be 23403 now it is 23421

    Adding new record will leave you with two records instead of one.

  • December 23, 2003 at 1:43 pm

    #487676

    What is the source of your new zip codes? We subscribe to a zip code database. Do a complete import every 3 months as zips relation to city can change over time as well as new zips being added.

    -Isaiah


    -Isaiah

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply