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:
1005;Ass. Réun. Com. Communau. Commune
1005;Brusselse Hoofdstedelijke Raad
1005;Conseil Region Bruxelles-Capitale
1006;Raad Vlaamse Gemeenschapscommissie
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:
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:
FIELDTERMINATOR = ';',
ROWTERMINATOR = 'n'
And now the table City is filled with 2903 records:
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:
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