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:
1: BULK
2: INSERT City
3: FROM 'd:\postcodes.csv'
4: WITH
5: (
6: FIELDTERMINATOR = ';',
7: ROWTERMINATOR = '\n'
8: )
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.