Importing Zip Codes

Question

Importing Zip Codes

charlesd

Default port

Points: 1427
More actions
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

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

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

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

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.

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

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

charlesd Default port Points: 1427 More actions · Answer 3

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.

jxflagg Hall of Fame Points: 3817 More actions · Answer 4

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

)

David Burrows SSC Guru Points: 65143 More actions · Answer 5

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.

charlesd Default port Points: 1427 More actions · Answer 6

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!!

amitelman SSC Enthusiast Points: 180 More actions · Answer 7

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.

iadams SSCommitted Points: 1970 More actions · Answer 8

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