I decided that I wanted to write a web based application using the completely free Microsoft tools (never mind why, more on that later). So I downloaded Visual Web Developer and SQL Server 2005.
One of the things I want my application to do is to filter data based on location, so I need the users to be able to select the country they want to filter on first. I went to the ISO site and downloaded the text file of country names and codes so that I could load it into the database.
I opened up Management Studio Express and created the table to load the data into. I then selected the database, right clicked and selected Tasks -> Import; or rather I would have, if it hadn't been missing! I looked round some obvious places. Nothing. I then checked the help files, no mention of an import facility.
Feeling a little apprehensive I started to search the Net, and sure enough, there I found confirmation that there is no import/export facility in the Express version! Yup, that's right, a database engine into which you can't (easily) bulk import data. Fantastic. :-(
Now what I should have done was uninstall the product and move straight to MySql, a free (for what I want) product that does everything I need, including bulk import. However, as I've said, I wanted to build the application using the free Microsoft tools, so I set about finding the approved Microsoft way of bulk loading data into your database, since you can't use the shiny Management Studio Express.
It turns out that what you are supposed to do is to use the command line utility BCP. You are kidding right?! Have you ever used the utility? It's a bit of a nightmare. You tell the software which table you want to use and where the datafile you want to load is (well that's what you do when you are loading a datafile like I was, it does other things too). The utility then goes on to ask you a number of obscure questions regarding the format of your file and if you get any of them wrong the upload fails. Great! :-)
Well, needless to say, I didn't use the utility. I wrote a C# app to load my data in less time than it would have taken me to work out how to use BCP. It did make me think however, how do none developers manage and what the heck was Microsoft thinking about putting out a database product (even a free one) without an import/export facility?