US Zip Code Table for SQL 2005

March 23, 2008

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#


How to check if an object exists in a SQL 2005 Database

February 13, 2008

When executing some SQL code on an object in database such as a table, stored procedure, or view, it is important to check to see if these objects currently exist in the database. It is important because it makes your code more reusable, especially if multiple developers will be running it on their local databases. Here is a simple line of SQL you can run to find various objects in a SQL 2005 database.

How to check if an object exists in a SQL 2005 Database

if exists (select * from sys.objects where [object_id] = object_id(‘the_name_of_your_object’))
begin
print ‘Hello World’
end


How to check if a stored procedure exists in a SQL 2005 Database

Say your Database contained several hundred stored procedures and you wanted to know if the stored procedure usp_GetNames existed. Simply run the following example from above:

if exists (select * from sys.objects where [object_id] = object_id(‘dbo.usp_GetNames’) )
begin
print ‘Hello World’
end

How to check if a view exists in a SQL 2005 Database

What if you wanted to create a new view called dbo.vw_OldCustomers, but wanted to see if that view existed before you created it? Again, run the same SQL code above.

if exists (select * from sys.objects where [object_id] = object_id(‘dbo.vw_OldCustomers’) )
begin
— Logic to create the view and populate it
end


-Flea#