Category Archives: SQL

Creating a database table with zip codes

Today I needed a database table in Sql Server that contains all Belgian zip codes and city names. Obviously I was not going to create this by hand, so I wanted to share how I did it because I think it’s a common requirement.

The first step was to find a list of zip codes and city names. On the site http://www.post.be/site/nl/residential/customerservice/search/postal_codes.html you can download this list in excel format. From this file I extracted the zipcode and city name and saved this as a csv-file, which then looked like:

1000;Brussel
1000;Bruxelles
1005;Ass. Réun. Com. Communau. Commune
1005;Brusselse Hoofdstedelijke Raad
1005;Conseil Region Bruxelles-Capitale
1005;Ver.Verg.Gemeensch.Gemeensch.Comm.
1006;Raad Vlaamse Gemeenschapscommissie
etc...

The second step was to create the database table that will contain this information, so I created a table called City with the following columns:

ZipCode, int
Name, varchar(100)

The next step was the actual import from the csv file into the table. To do this, I used the bulk copy technique: just open a new query window and execute the following statement:

BULK
INSERT City
FROM 'd:postcodes.csv'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = 'n'
)

And now the table City is filled with 2903 records:

City table

As a last step I added another column as the primary key:

Id, int, primary key (identity)

And now each entry has a primary key:

City table

I think this is probably the easiest way to create a table with zip codes and city names. Except maybe… if someone would give you the script to create and fill the City table 🙂 Well here it is (it seems I can only upload doc files, sorry for that): script_generate_zip_codes_belgium

Advertisements