US Zip Code Table for SQL 2005

I am working on a web application that requires that the user to enter in a zip code and then the city and state would be returned. This type of functionality is quite common, but for some of us developers, we don’t want to pay for the data needed to make this happen. Granted, it didn’t take me too long to find a free table of zip codes, but I did have to do some minor work to hook it all up. So, just to add to the bounty of the Internet, I’ve uploaded the CSV file to one of my servers for you to download along with instructions below for inserting it into your SQL 2005 database. Since this is in a CSV file, you can also use it for MySQL or any other database.

  1. Click here for the ZIPCODE CSV file. Download it to the root of C, just something simple.
  2. Run the following SQL to create the ZipCode table in your database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ZipCode]
(
[ZipCode] [varchar](10) NOT NULL,
[Latitude] [varchar](50) NOT NULL,
[Longitude] [varchar](50) NOT NULL,
[City] [varchar](200) NOT NULL,
[State] [varchar](50) NOT NULL,
[County] [varchar](70) NOT NULL,
[ZipClass] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

  1. Run the Bulk Insert command to load the CSV file into the table:

BULK INSERT ZipCode
FROM ‘C:\zipcodes.txt’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
GO

There ya go, you now have a free zip code table of all 42,000+ United States zip codes! Note that you might need to change the commas and ‘ ‘ marks; sometimes these characters are formatted differently when posted on the web and it can cause havoc in SQL because SQL does not recognize the characters. Just be prepared to change them!

-Flea#

Advertisements

5 Responses to US Zip Code Table for SQL 2005

  1. John Wells says:

    THANK YOU!
    Great help

  2. Bill Kuhn says:

    Thanks! Very handy to have this (code and data) in one place!

  3. Corey Magin says:

    Thanks. My only problem is that my server host doesn’t give me permission for the bulk insert command

  4. diesel says:

    Awesome. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: