So as one of my sites grew I quickly found out that my zip code database was horribly incomplete with only 33234 records and it even had duplicates for some zip codes. I did some searching and came across a post about a zip code csv file but the post goes to what looks like an empty page with no file. A little digging lead me to the zip code file download page that gives you a csv with entries like this:
"78701","Austin","TX","30.27127","-97.74103","-6","1"
Awesome! This csv file has 43191 unique records and has entries for each zip code my users have reported as missing. I started by looking up how to import a csv into MSSQL and followed an article called Using BULK INSERT to Load a Text File from which I came up with my query.
BULK INSERT ZipCodes FROM 'c:\zipcodes.csv' WITH (FIELDTERMINATOR = '"",""', ROWTERMINATOR = '\n')
It didn’t work and after a couple hours of trial and error I threw my hands up in the air and abandoned the bulk insert and fell back on another option of using phpMyAdmin. It sounds crazy but I setup the database in MySQL and imported the csv on the first attempt! Now to get it back to to MSSQL I exported the database as single row imports that looked like this:
INSERT INTO `ZipCodes` VALUES('78701', 'Austin', 'TX', '30.27127', '-97.74103', -6, 1);
A few quick find and replaces to change the database name and remove the ` characters and I was able to run them as queries on my MSSQL database in blocks of 5500 to prevent timeouts.
INSERT INTO [databaseName].[dbo].[ZipCodes] VALUES('78701', 'Austin', 'TX', '30.27127', '-97.74103', -6, 1);