Populating the NZCities table in The Raiser's 7

The Raiser's Edge has a few niggly quirks, one of which is a special set of tables for cities and suburbs in New Zealand. For reasons that no one seems to want to explain (my guess is no one actually remembers why), the normal "City" field was not enough for New Zealand, and so a second field was set up to accommodate New Zealand Cities. Now, I've lived in New Zealand, and I can tell you that their cities are not so dissimilar from cities anywhere else... but I digress. For most fields in The Raiser's Edge, you have the option of establishing whether that field is a free text field, or whether it requires a lookup table (think, dropdown menu). What truly makes the NZ Cities and NZ Suburbs issue in RE7 absurb, is that they are forced to be "lookup only". Which means, you guessed it, you are pretty much expected to populate a table full of all the Cities and Suburbs from New Zealand. This situation is made all the more frustrating if you use NetCommunity, which relies on the field settings (and dropdown table values) from The Raiser's Edge. That said, BBNC only relies on the RE7 fields in some cases, not all.  For reasons.... ?

For our part we've decided to generally ignore the NZ suburbs field. If people provide them to us online, brilliant, but we're not fussed about populating this table. The cities table is a bit of a pain though, because it shows online and so we essentially have to fill it with values.

Now, I'm not going to suggest that you should do this, but if you understood the potential risks to your database, and you were so inclined to take matters like that into your own hands, then you might want to run a SQL script to update the values in this table, and save yourself the trouble of entering 500 cities into The Raiser's Edge.

And if you were looking for such a script online, you might find one here, that appears to populated with the NZ towns list from Wikipedia. If you did happen to use such a script at this (not saying you should) you'd likely want to replace any instances of databasename with your own RE7 database, any instances of datestamp with a valid datetime, and any instances of userid with a valid RE user id number. Just sayin.